I really enjoyed this course and have learned a lot. I especially liked how I was given liberty to select how and when to learn, to some degree, and was allowed to be able to explore other areas of interest. It felt like a class that was tailor-fit for me, as opposed to the courses that have strict topics and deadlines, in a cookie cutter kind of way.
In this final paper, I will attempt to summarize the semester of learning. I will not only mention what I learned, but why I liked it and how it will benefit me in the future. I will touch on four main topics: information architecture, database management, event logging and system monitoring, and teamwork and communication skills.
Enterprise Architecture
1. Understand enterprise information architecture so you can articulate a rational position on when and why various supporting methodologies, tools, and protocols are helpful or not helpful in a particular business environment. Major information architecture topics you should know well include the following:
a. Enterprise application integration
This is the idea of integrating subsystems in an organization, to create one comprehensive system. This is a good idea, when implemented properly, because it will increase efficiency. I think the main reason to integrate the systems it so automate processes for convenience and efficiency. Otherwise, it is not worth it.
This is a very strategic aspect of a company. A company would really benefit from the increased efficiency if it could focus less on the systems, because they are working well together and require little human intervention, and focus more on other aspects of the business go gain a competitive advantage. If this is not the case, that is, if the integration doesn’t work toward bettering the business and increasing advantages, it probably isn’t worth it.
The key word is efficiency, and that is a difficult thing to achieve. Integration takes a lot of planning, good execution, and maintenance. But if the business benefit can be obtained, and it fits within strategy, the integration of systems can be great.
I am very interested in business process efficiency. I think I’m going into a career where my job would be to analyze a system for a company and rate it. Then, if needed, give recommendations for improvement and implement that solution. That is the kind of problem solving that I like. So learning about enterprise application integration was very interesting for me.
b. Service oriented architecture
This is the idea of having loosely coupled modules accessible and working together. An organization has different modules that provide specific services to perform a business function. These services are accessible through a network because the service and related protocols are standardized. An example is the web service, which I will talk about later.
I liked learning about this because programming is a little overwhelming for me. When I think back to Intex, and the big project we had to do, I usually decide I never want to program. But when I break applications down into functions, it seems much more manageable. Like something I can do, on task at a time, and make sure they all work together. If not, I can troubleshoot one module, and my whole application isn’t broken.
c. Model-driven architecture and executable models
This was a really cool idea, and I liked learning about it because it is something I am hoping is the future of programming. It uses software to transform human-created models, which address database relationships and business requirements, into code. I would love to create programs this way, because it would eliminate some of what I think is very tedious, because I’m not that good at it.
I looked into some of the tools that are out there for MDA, and most of it seemed like it was pretty new. It would make sense for some companies, but is not feasible for others. I would like to try this in the future and then form an opinion, but for now, it’s cool
d. Web services
I learned the most in this section of the class. It was great because I learned a lot about many different things. I learned .Net and created my first application ever using that platform, and I like It. I learned how to search, find and use published web services. I learned to create my own, and learned all about the documentation of web services in the WSDL.
I couldn’t get the weather web service to work for a while, so in the mean time, to figure out what I was doing wrong, I created multiple other services and had a blast. I finally got mine to work, and felt a great sense of accomplishment because I made it work on my own, taught others in the class how to do it, and had a cool finished product; a published web service.
My group also did our research project on implementing web services in a variety of different ways, and I learned a lot from that as well. I will address that later on in the paper.
Database Management
2. Understand t he key role of database management systems in enterprise information architecture and demonstrate the major elements of DBMS optimization, including DBMS server tuning and SQL query optimization.
This section was a little more difficult for me because I felt like I had to do a lot or catch up before I could start working on the project because I had so little exposure to SQL in the past. I fought through it and learned a lot in the process.
One thing that was difficult is that I was working on such a small scale, that the improvements I suggested were didn’t save much time. I think if the suggestions were applied to a sizable database, the improvements in efficiency would be drastic, and it would be cool to see exactly what those are.
I don’t know that any of my jobs in the future will be technical enough to get my hands on database queries, but I definitely think it will be beneficial because I might be dealing with the people who do, and now I can understand a little better their language.
Event Logging
3. Understand the importance of event logging and system monitoring within an enterprise-class system, and demonstrate how to put a logging/monitoring system in place.
This concept was pretty new to me because I had never used an event logger. The closest I had seen was some programs that use logs to document errors, access, or anything really. I see the usefulness in Java programming. Especially when there is messaging. These messages can be very useful to administrators.
I some fraud detection software recently that monitored all of the files I viewed and any changes I made to them. This could be used in court if needed. Also, I know the big ERP systems have loggers so changes to the system, and access for that matter, can be tracked. This is a control.
Since I’m not a great programmer, logging would be great for debugging. That is the most useful application for me now.
Teamwork
4. Enhance your teamwork and communication skills by delivering a solid group paper and an in-class presentation on an ISys 532 topic.
My team chose WSDL as a topic to research. But after we learned about SOA and web services in class, we wanted to broaden our scope and learn more about some aspects that weren’t taught in class. So we decided to learn about all of the different ways to implement web services. I was assigned the WSDL and SOAP sections of the paper, and learned a great deal about them that I didn’t learn in class.
My team worked well together. There was one instance when we decided to modify our topic a bit. We had a very frank discussion about what each of us would like to learn about, and were able to come to a consensus. Luckily, we divided the paper up in to three parts, and there were three group members, and each was able to write on the section that most interested him.
The group was very efficient, and honest in giving feedback. I think we ended up with a good finished product and we were happy with the effort each group member put into the project.
Tuesday, April 24, 2007
Wednesday, April 18, 2007
MDA
MDA stands for Model Driven Architecture and was created by the Object Management Group with the goal of making it easier to develop software with less development and chance for human error. It also has the goal of eliminating the need for middleware, and separating the design from the development. It creates software that is platform independent.
Here is how it works. A model of a software solution is created. That model is then used to create the software in any of a number of different platforms. The idea is that it doesn’t matter what the platform is that the software ends up in. The important thing is that the model is correct, and then used to develop exactly what is needed. It is a very cool idea.
Of course this means that the models need to be very accurate, and for this reason there is a standard for model development before it can be use in development. So basically, the human sets out the specifications and gives the requirements, and the code is then generated.
This is valuable because requirements and specifications can be laid out for a given software project in the form of a standardized model. Then the code will be created for any platform you need; .Net, Java and others. This allows you to more easily test software and select an appropriate programming language.
In MDA, more of the time and effort will be placed in the planning and requirements phase of software development, not in the actual coding. This is better for companies, especially where code-happy developers are involved, and wand to get right into the code. This allows for a very high level view of the functionality of the software, which will in turn, result in a more specific and complete final product.
There are many tools for developing. The human element of MDA is in the development of the models, but there are even tools that will create the models. There are analysis tools that will inspect the models for completeness, and tools used to transform the models into actual code. There are many vendors that offer these MDA tools, many of which are OMG members. Some of them include the Voyant Group, Softteam, Soluta.net and Compuware.
MDA 2
As a representative of an organization were information systems are vital, I would be very skeptical of MDA, and I would do a lot of research and testing before deciding to, or not to, implement MDA in my company. I would make MDA prove its worth.
I think MDA is good for a company that does a lot of development, and has many programmers on its payroll. The main benefit would be to eliminate the high costs of human programming, an the costs will be higher, and the benefits greater if there are a large amount of programmers who will not be needed after the implementation of MDA. I’ll pretend the company I represent has many programmers.
I feel that MDA is the next big thing for software development. But I’m not sure if I’m ready for it yet. Some of the criticisms of MDA is that it is so new, and not fully developed, so the standards aren’t quite standardized yet. I would be hesitant to implement it, only to have to change major components in the future to conform. Also, not many organizations are using it, and it is relatively new, so it hasn’t been proven to stand the test of time.
I would look very carefully at some of the companies who have successfully, and not so successfully implemented MDA. I would like to find a company similar to mine who works with it and it works for them. I would also to hear the criticisms of MDA from the companies who abandoned it. I would look very carefully at what other companies have to say.
I will look at a company, Navitare, who develops software for airline reservations. They produce code as a core competency. There are many developers, all over the world. And for every developer, there is a business analyst/consultant who is in charge of meeting with customers and gathering requirements definitions and communicating them to developers.
Navitare has been in business long enough, and has experience enough, that they have software development for reservations down. The product sold to airlines is very similar, and each customer modifies the software to tailor fit their needs. I see pros and cons for implementing MDA for this company.
One reason not to implement is because Navitare already h
as the software developed, and only minor changes are made to the code. The development of the base software is complete. MDA tools wouldn’t be used much because they already have software that matches their model.
One reason to use MDA is in case of major changes to the software in the future. Maybe they would like to have a variety of base codes to give their customers more options. In this case, they would want to create more models. Another reason is if customers are asking for the software in various different platforms, because their internal IT group maintains the software. With MDA tools, the software output could be in any platform the customer wanted.
I think MDA is good for a company that does a lot of development, and has many programmers on its payroll. The main benefit would be to eliminate the high costs of human programming, an the costs will be higher, and the benefits greater if there are a large amount of programmers who will not be needed after the implementation of MDA. I’ll pretend the company I represent has many programmers.
I feel that MDA is the next big thing for software development. But I’m not sure if I’m ready for it yet. Some of the criticisms of MDA is that it is so new, and not fully developed, so the standards aren’t quite standardized yet. I would be hesitant to implement it, only to have to change major components in the future to conform. Also, not many organizations are using it, and it is relatively new, so it hasn’t been proven to stand the test of time.
I would look very carefully at some of the companies who have successfully, and not so successfully implemented MDA. I would like to find a company similar to mine who works with it and it works for them. I would also to hear the criticisms of MDA from the companies who abandoned it. I would look very carefully at what other companies have to say.
I will look at a company, Navitare, who develops software for airline reservations. They produce code as a core competency. There are many developers, all over the world. And for every developer, there is a business analyst/consultant who is in charge of meeting with customers and gathering requirements definitions and communicating them to developers.
Navitare has been in business long enough, and has experience enough, that they have software development for reservations down. The product sold to airlines is very similar, and each customer modifies the software to tailor fit their needs. I see pros and cons for implementing MDA for this company.
One reason not to implement is because Navitare already h
as the software developed, and only minor changes are made to the code. The development of the base software is complete. MDA tools wouldn’t be used much because they already have software that matches their model.
One reason to use MDA is in case of major changes to the software in the future. Maybe they would like to have a variety of base codes to give their customers more options. In this case, they would want to create more models. Another reason is if customers are asking for the software in various different platforms, because their internal IT group maintains the software. With MDA tools, the software output could be in any platform the customer wanted.
MDA 3
I examined iQgen. I couldn’t find any pricing information on the website. The product is very interesting. Here is an excerpt from their own product overview.
“Able to generate every textual software artefact, including, but not limited to, Java, C++, C and C# source code, XML files (e.g. deployment descriptors), configuration information, make or Ant build scripts, test drivers and test data “
There was one typo in the quote which was odd. The software is developed in Java, and products code in many other languages. I looked a few different options, and this one seems good.
Here is a link to the product documentation
http://www.innoq.com/resources/iQgen.pdf
“Able to generate every textual software artefact, including, but not limited to, Java, C++, C and C# source code, XML files (e.g. deployment descriptors), configuration information, make or Ant build scripts, test drivers and test data “
There was one typo in the quote which was odd. The software is developed in Java, and products code in many other languages. I looked a few different options, and this one seems good.
Here is a link to the product documentation
http://www.innoq.com/resources/iQgen.pdf
DB Opt
Here is a link to my complete report.
gabrielcorotan.com/sql.doc
I have summariezed it in this blog entry.
All about indexes – what I learned
1. Index – creates a separate table with the keys for numbers
a. Don’t need to full table search, used index to sort
b. Makes it easier when joining
c. They are generally smaller that the tables they were created from, because they contain only the fields of interest. If you are not searching on a column, don’t include it. That also makes for faster queries.
d. Three types
i. Unique – all of the values in the index need to be unique, ensures original columns.
ii. Full-text – can compare a word to the root of the word, or even find words that are similar to each other.
iii. Spatial – used for determining difference between points on space, must be used with a spatial database and spatial queries
e. B tree – sometimes known as a balanced tree. It is balanced because all nodes have the same number of jumps back to the root. It is a data structure that allows for insertions and deletions, and makes adjustments to the structure of the tree when these occur. Adjustments don’t always occur, which means the tree isn’t always in balance, but when it needs, it reconfigures, and balances itself out. It is used because is saves time because it doesn’t have to search all of the nodes in the tree, because they are organized. It simply has to grab the correct node.
f. Hash – A table of hashes is created. The foreign keys are used to create the hash, which is a smaller, easier-to-compare element. Instead of looking for the matching keyword, it creates a hash, and compares that hash to the hash table (index). One major drawback that I see is that there needs to be an encryption happening, at least the first time through, to create the hash, and encryption takes time and processing power. I think B tree sounds faster.
1. Split the set of queries into two groups: those that likely need no further optimization than simply adding indexes, and those that are more complex.
a. Here is a link for a full list of the queries separated into groups.
gabrielcorotan.com/mpr.doc
2. Recommend indexes that should be added to the stripped-down schema I've given you.
a. Index all of the primary keys in all of the tables
b. Hashes are good for reducing storage space, but consume a lot of memory, and use resources to encrypt
i. They are better for dynamic tables, and for tables that have large amounts of data, and they are modified often. B trees are not for dynamic tables because it may require rearranging the structure. Here are the tables in the database that should use a hashed index.
'activity'
'adjacentcampground'
'agentstake'
'amenity'
'announcement'
'assignedto'
'camp'
'campground'
'camppicture'
'camprule'
'camprulecategory'
'campsite' table
'codes'
'direction'
'facility'
'form'
'generalsite'
'glossary'
'nextpriorityschedule'
'priorityschedule'
'rulecategory'
'ruletable'
'stake'
'ward'
c. B trees are good for tables that need to be displayed, and are more static
d. Below a list of the tables in the database and the type of index that should be created for it:
i. Some table should use a b tree index because it is more static. There will be occasional additions and modifications to the table, but for the most part, it will be accessed and displayed more than it is modified. The b tree will allow faster search time. Also, this will be one of the most viewed tables on the site.
'faq'
'reservation'
'reservationdate'
'reservationforcampsite'
'unavailabledate'
'usercamp'
'usertable'
3. From the complex set, pick three queries to tune. Modify the queries and/or the database to optimize those queries.
SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
CAST(r.startDate AS DateTime) AS arrival,
CAST(r.endDate AS DateTime) AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE CAST(w.wardID AS NVARCHAR)=r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((CAST(r.startDate AS DateTime) >= 'firstDate' AND
CAST(r.startDate AS DateTime) <= 'lastDate') OR (CAST(r.endDate AS DateTime) >= 'firstDate' AND
CAST(r.endDate AS DateTime) <= 'lastDate')) AND r.campID=cid AND rfc.siteID=sid ORDER BY departure SELECT DISTINCT category, crc.catID FROM CampRuleCategory crc, CampRule cr WHERE crc.catID=cr.catID AND campID=cid SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType, CAST(r.startDate AS DateTime) AS arrival, CAST(r.endDate AS DateTime) AS departure, (SELECT stakeName FROM Stake s WHERE s.stakeID=r.stake) AS stakeName, (SELECT wardName FROM Ward w WHERE CAST(w.wardID AS NVARCHAR)=r.ward) AS wardName FROM Reservation r INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid) WHERE ((CAST(r.startDate AS DateTime) >= 'firstDate' AND
CAST(r.startDate AS DateTime) <= 'lastDate') OR (CAST(r.endDate AS DateTime) >= 'firstDate' AND
CAST(r.endDate AS DateTime) <= 'lastDate')) AND r.campID=cid AND rfc.siteID=sid
ORDER BY departure
4. Are there any database schema changes you recommend?
a. Every table should be in MyISAM - it is faster and uses much more overhead
b. I would be sure that on all fixed length values, such as zip codes or yes/no fields, I would use the appropriate data type. For example, yes/no only needs a Boolean, and that will save space compared to the char data type.
c. Eliminate the varchars for the same reason as above. This will tighten up the schema, and reduce the size on disk and improve query speeds.
d. I would change some of the larger tables by splitting the table. This will help when the table is large, and is accessed frequently, but there are only a few fields that are ever searched on. That will allow the query to run much faster because it won’t be messing with searching and joining fields that it doesn’t need.
gabrielcorotan.com/sql.doc
I have summariezed it in this blog entry.
All about indexes – what I learned
1. Index – creates a separate table with the keys for numbers
a. Don’t need to full table search, used index to sort
b. Makes it easier when joining
c. They are generally smaller that the tables they were created from, because they contain only the fields of interest. If you are not searching on a column, don’t include it. That also makes for faster queries.
d. Three types
i. Unique – all of the values in the index need to be unique, ensures original columns.
ii. Full-text – can compare a word to the root of the word, or even find words that are similar to each other.
iii. Spatial – used for determining difference between points on space, must be used with a spatial database and spatial queries
e. B tree – sometimes known as a balanced tree. It is balanced because all nodes have the same number of jumps back to the root. It is a data structure that allows for insertions and deletions, and makes adjustments to the structure of the tree when these occur. Adjustments don’t always occur, which means the tree isn’t always in balance, but when it needs, it reconfigures, and balances itself out. It is used because is saves time because it doesn’t have to search all of the nodes in the tree, because they are organized. It simply has to grab the correct node.
f. Hash – A table of hashes is created. The foreign keys are used to create the hash, which is a smaller, easier-to-compare element. Instead of looking for the matching keyword, it creates a hash, and compares that hash to the hash table (index). One major drawback that I see is that there needs to be an encryption happening, at least the first time through, to create the hash, and encryption takes time and processing power. I think B tree sounds faster.
1. Split the set of queries into two groups: those that likely need no further optimization than simply adding indexes, and those that are more complex.
a. Here is a link for a full list of the queries separated into groups.
gabrielcorotan.com/mpr.doc
2. Recommend indexes that should be added to the stripped-down schema I've given you.
a. Index all of the primary keys in all of the tables
b. Hashes are good for reducing storage space, but consume a lot of memory, and use resources to encrypt
i. They are better for dynamic tables, and for tables that have large amounts of data, and they are modified often. B trees are not for dynamic tables because it may require rearranging the structure. Here are the tables in the database that should use a hashed index.
'activity'
'adjacentcampground'
'agentstake'
'amenity'
'announcement'
'assignedto'
'camp'
'campground'
'camppicture'
'camprule'
'camprulecategory'
'campsite' table
'codes'
'direction'
'facility'
'form'
'generalsite'
'glossary'
'nextpriorityschedule'
'priorityschedule'
'rulecategory'
'ruletable'
'stake'
'ward'
c. B trees are good for tables that need to be displayed, and are more static
d. Below a list of the tables in the database and the type of index that should be created for it:
i. Some table should use a b tree index because it is more static. There will be occasional additions and modifications to the table, but for the most part, it will be accessed and displayed more than it is modified. The b tree will allow faster search time. Also, this will be one of the most viewed tables on the site.
'faq'
'reservation'
'reservationdate'
'reservationforcampsite'
'unavailabledate'
'usercamp'
'usertable'
3. From the complex set, pick three queries to tune. Modify the queries and/or the database to optimize those queries.
SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
CAST(r.startDate AS DateTime) AS arrival,
CAST(r.endDate AS DateTime) AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE CAST(w.wardID AS NVARCHAR)=r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((CAST(r.startDate AS DateTime) >= 'firstDate' AND
CAST(r.startDate AS DateTime) <= 'lastDate') OR (CAST(r.endDate AS DateTime) >= 'firstDate' AND
CAST(r.endDate AS DateTime) <= 'lastDate')) AND r.campID=cid AND rfc.siteID=sid ORDER BY departure SELECT DISTINCT category, crc.catID FROM CampRuleCategory crc, CampRule cr WHERE crc.catID=cr.catID AND campID=cid SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType, CAST(r.startDate AS DateTime) AS arrival, CAST(r.endDate AS DateTime) AS departure, (SELECT stakeName FROM Stake s WHERE s.stakeID=r.stake) AS stakeName, (SELECT wardName FROM Ward w WHERE CAST(w.wardID AS NVARCHAR)=r.ward) AS wardName FROM Reservation r INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid) WHERE ((CAST(r.startDate AS DateTime) >= 'firstDate' AND
CAST(r.startDate AS DateTime) <= 'lastDate') OR (CAST(r.endDate AS DateTime) >= 'firstDate' AND
CAST(r.endDate AS DateTime) <= 'lastDate')) AND r.campID=cid AND rfc.siteID=sid
ORDER BY departure
4. Are there any database schema changes you recommend?
a. Every table should be in MyISAM - it is faster and uses much more overhead
b. I would be sure that on all fixed length values, such as zip codes or yes/no fields, I would use the appropriate data type. For example, yes/no only needs a Boolean, and that will save space compared to the char data type.
c. Eliminate the varchars for the same reason as above. This will tighten up the schema, and reduce the size on disk and improve query speeds.
d. I would change some of the larger tables by splitting the table. This will help when the table is large, and is accessed frequently, but there are only a few fields that are ever searched on. That will allow the query to run much faster because it won’t be messing with searching and joining fields that it doesn’t need.
Queries classified
I know this is annowing, but i couldn't figure out how to linc a document into my blog entry. So here it is.
Simple
Table: Activity
SELECT * FROM Activity WHERE campID='cid'
SELECT MAX(activityID) AS m FROM Activity
Table: Amenity
SELECT * FROM Amenity WHERE groundID=gid
SELECT * FROM Amenity WHERE groundID=gid ORDER BY amenityName
Table: Announcement
SELECT * FROM Announcement
WHERE campID <= 0 AND
(annBeginDate <= GETDATE() OR annBeginDate = '' OR annBeginDate IS NULL) AND
(annEndDate >= GETDATE() OR annEndDate = '' OR annEndDate IS NULL)
ORDER BY announceID DESC
SELECT * FROM Announcement
WHERE campID=cid AND
(annBeginDate <= GETDATE() OR annBeginDate = '' OR annBeginDate IS NULL) AND
(annEndDate >= GETDATE() OR annEndDate = '' OR annEndDate IS NULL)
ORDER BY announceID DESC
SELECT announceID, announceTitle, announceText,
CAST(MONTH(annBeginDate) AS varchar) + '/' +
CAST(DAY(annBeginDate) AS varchar) + '/' +
CAST(YEAR(annBeginDate) AS varchar) annBeginDate,
CAST(MONTH(annEndDate) AS varchar) + '/' +
CAST(DAY(annEndDate) AS varchar) + '/' +
CAST(YEAR(annEndDate) AS varchar) annEndDate
FROM Announcement
WHERE campID='cid'
Table: Camp
SELECT * FROM Camp ORDER BY name
SELECT * FROM Camp WHERE campID='cid'
SELECT COUNT(*) FROM Camp
SELECT allowYWSaturdays FROM Camp WHERE campID=cid
SELECT campID, name FROM Camp
SELECT campID, name FROM Camp ORDER BY name
SELECT campID, name FROM Camp WHERE campID='cid'
SELECT descrip, pic FROM Camp WHERE campID=cid
SELECT directionmap FROM Camp WHERE campID=cid
SELECT mappic FROM Camp WHERE campID=cid
SELECT name FROM Camp WHERE campID='cid'
SELECT name, contactEmail, campEmail FROM Camp WHERE campID=cid
SELECT firstScheduled, lastScheduled FROM Camp WHERE campID=cid
SELECT nextfirstScheduled, nextlastScheduled FROM Camp WHERE campID=cid
SELECT COUNT(name),
(SELECT COUNT(*) FROM Reservation WHERE confirmed<>'N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE confirmed='N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation) AS Total,
(SELECT COUNT(*) FROM UnavailableDate) AS Unavailable
FROM Camp
Complex
SELECT name, acceptReservations,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed<>'N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed='N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID) AS Total,
(SELECT COUNT(*) FROM UnavailableDate WHERE UnavailableDate.campID=c.campID) AS Unavailable,
(SELECT COUNT(*) FROM Campsite s, Campground g WHERE s.groundID=g.groundID AND g.campID=c.campID) AS Sites,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date0)) As Day0,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date1)) As Day1,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date2)) As Day2,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date3)) As Day3,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date4)) As Day4,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc
WHERE r.reservID=rfc.reservID AND r.startDate=r.endDate AND
r.campID=c.campID AND r.startDate IN (date5)) AS Day5
FROM Camp c
ORDER BY name
SELECT acceptReservations, nextacceptReservations,
CAST(MONTH(firstScheduled) AS VARCHAR) + '/' +
CAST(DAY(firstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(firstScheduled) AS VARCHAR) AS firstScheduled,
CAST(MONTH(lastScheduled) AS VARCHAR) + '/' +
CAST(DAY(lastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(lastScheduled) AS VARCHAR) AS lastScheduled,
allowYWSaturdays,
CAST(MONTH(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextfirstScheduled) AS VARCHAR) AS nextfirstScheduled,
CAST(MONTH(nextlastScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextlastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextlastScheduled) AS VARCHAR) AS nextlastScheduled,
currentSeason, nextSeason
FROM Camp
WHERE campID=cid
SELECT acceptReservations, nextacceptReservations,
CAST(MONTH(firstScheduled) AS VARCHAR) + '/' +
CAST(DAY(firstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(firstScheduled) AS VARCHAR) AS firstScheduled,
CAST(MONTH(lastScheduled) AS VARCHAR) + '/' +
CAST(DAY(lastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(lastScheduled) AS VARCHAR) AS lastScheduled,
allowYWSaturdays,
CAST(MONTH(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextfirstScheduled) AS VARCHAR) AS nextfirstScheduled,
CAST(MONTH(nextlastScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextlastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextlastScheduled) AS VARCHAR) AS nextlastScheduled,
allowYWSaturdays
FROM Camp
WHERE campID=cid
Table: Campground
SELECT * FROM Campground WHERE campID=cid ORDER BY groundName
SELECT * FROM Campground WHERE groundID=gid ORDER BY groundName
SELECT MAX(groundID) AS m FROM Campground
SELECT MIN(groundID) FROM Campground WHERE campID='cid'
SELECT groundID, groundName FROM Campground WHERE campID=cid ORDER BY groundName
Table: CampPicture
SELECT * FROM CampPicture WHERE campID='cid'
SELECT MAX(picID) AS m FROM CampPicture
SELECT picloc FROM CampPicture WHERE campID=cid
Table: CampRule
SELECT * FROM CampRule WHERE catID=cid
Table: CampRuleCategory
SELECT * FROM CampRuleCategory WHERE campID='cid'
SELECT MIN(catID) FROM CampRuleCategory WHERE campID='cid'
Table: Campsite
SELECT * FROM Campsite WHERE groundID='gid' ORDER BY siteName
SELECT COUNT(*) FROM Campsite WHERE groundID='gid'
SELECT siteCapacity FROM Campsite WHERE groundID=gid
SELECT siteCapacity FROM Campsite WHERE siteID='sid'
Table: Codes
SELECT code, description FROM Codes WHERE ctype='Activity' ORDER BY pos
SELECT code, description FROM Codes WHERE ctype='Priority' ORDER BY pos
SELECT pos FROM Codes WHERE ctype='Tracking' AND code='GA'
Table: Direction
SELECT * FROM Direction
SELECT * FROM Direction WHERE campID='cid'
Table: Facility
SELECT * FROM Facility WHERE campID='cid'
SELECT MAX(facilityID) AS m FROM Facility
Table: FAQ
SELECT * FROM FAQ ORDER BY pos
Table: Form
SELECT * FROM Form
Table: GeneralSite
SELECT * FROM GeneralSite
SELECT about, aboutpic, reserveInfo FROM GeneralSite
SELECT map FROM GeneralSite
Table: Glossary
SELECT * FROM Glossary ORDER BY term
Table: PrioritySchedule or NextPrioritySchedule
SELECT MAX(priorityallowed) FROM PrioritySchedule WHERE asOfDate < GETDATE()
SELECT MAX(priorityallowed) FROM NextPrioritySchedule WHERE asOfDate < GETDATE()
-- n is an integer between 1 and 6 inclusive
SELECT asOfDate FROM PrioritySchedule
WHERE priorityallowed='n' AND DATEPART(month,asOfDate) > 3 AND DATEPART(year, asOfDate) <= DATEPART(year,GETDATE())
SELECT asOfDate FROM NextPrioritySchedule
WHERE priorityallowed='n' AND DATEPART(month,asOfDate) > 3 AND DATEPART(year, asOfDate) <= DATEPART(year,GETDATE())
SELECT asOfDate FROM PrioritySchedule WHERE priorityAllowed=n
SELECT asOfDate FROM NextPrioritySchedule WHERE priorityAllowed=n
Table: Reservation
SELECT * FROM Reservation WHERE reservID=rid
SELECT MAX(reservID) FROM Reservation
SELECT contactEmail FROM Reservation WHERE reservID=rid
SELECT DISTINCT endDate FROM Reservation WHERE campID=id ORDER BY endDate
Table: ReservationDate
SELECT * FROM ReservationDate WHERE reservDate='date' AND reservID='rid'
SELECT reservID FROM ReservationDate WHERE reservDate='date'
SELECT reservDate FROM ReservationDate WHERE reservID='rid'
Table: ReservationForCampsite
SELECT * FROM ReservationForCampsite WHERE siteID='sid' AND reservID='rid'
Table: RuleCategory
SELECT * FROM RuleCategory
SELECT MIN(catID) FROM RuleCategory
Table: RuleTable
SELECT * FROM RuleTable
SELECT * FROM RuleTable WHERE catID='cid'
Table: Stake
SELECT stakeID, stakeName FROM Stake WHERE stakeID > 1 ORDER BY stakeName
SELECT stakeName FROM Stake WHERE stakeID=sid
Table: UnavailableDate
SELECT unavailDate FROM UnavailableDate WHERE campID=cid
SELECT unavailDate FROM UnavailableDate WHERE campID=cid ORDER BY unavailDate
Table: UserCamp
SELECT * FROM UserCamp WHERE userID='uid'
SELECT campID FROM UserCamp WHERE userID='uid'
Table: UserTable
SELECT * FROM UserTable WHERE userID='uid' AND password='password'
SELECT * FROM UserTable WHERE userID='uid'
SELECT userID, name FROM UserTable
Table: Ward
SELECT wardID, wardName, unittype FROM Ward WHERE stakeID='sid' ORDER BY wardName
SELECT wardName FROM Ward WHERE wardID=wid
Tables: AgentStake joined with others
SELECT AgentStake.*, Stake.stakeName
FROM AgentStake, Stake
WHERE campID=cid AND AgentStake.stakeID=Stake.stakeID
SELECT stakeName, c.name AS name, g.stakeID AS asi, s.stakeID AS si
FROM Stake s, Camp c, AssignedTo a, AgentStake g
WHERE s.stakeID=a.stakeID AND c.campID=a.campid AND g.campID=c.CampID
ORDER BY stakeName, Camp.name
SELECT stakeName, c.name AS name, g.stakeID AS asi, s.stakeID AS si
FROM Stake s, Camp c, AssignedTo a, AgentStake g
WHERE s.stakeID=a.stakeID AND c.campID=a.campid AND g.campID=c.CampID
ORDER BY c.name, stakeName
SELECT startDate, endDate, numPeople, groupType,
ward, reservedBy, r.campID,
r.contactPhone, contactAddress,
r.contactEmail, specialRequests,
managerComments, confirmed, totCost, c.name as propertyName,
managerName, c.contactPhone AS manPhone,
c.contactEmail as manEmail,
g.name AS spName, street, city, state, zip,
s.stakeName AS ssname, a.stakeName AS asname
FROM Reservation r, Camp c, AgentStake g, Stake s, Stake a
WHERE r.campID=c.campID AND c.campID=g.CampID AND
s.stakeID=stake AND a.stakeID=g.stakeID AND reservID=rid
Tables: AssignedTo and {Stake or Ward}
SELECT s.stakeID, stakeName
FROM AssignedTo a, Stake s
WHERE a.stakeID=s.stakeID AND a.campID='cid'
ORDER BY stakeName
SELECT wardID, wardName, unitType
FROM AssignedTo a, Ward w
WHERE w.stakeID=sid AND w.stakeID=a.stakeID AND a.campID=cid +
ORDER BY wardName
Tables: Camp, Campground, Campsite, and ReservationForCampsite
SELECT s.siteID, s.siteName, s.siteCapacity, g.groundName, r.reservID
FROM Campsite s
INNER JOIN Campground g ON (s.groundID=g.groundID)
INNER JOIN Camp c ON (g.campID=c.campID)
LEFT JOIN ReservationForCampsite r ON (r.reservID=" + editMode AND r.siteID=s.siteID)
WHERE c.campID=cid AND c.campID=g.campID AND g.groundID=s.groundID
ORDER BY g.groundName, s.siteName
Tables: Camp and Reservation
SELECT r.reservID, r.startdate, r.enddate, r.grouptype, r.stake, r.ward,
r.contactPhone, r.contactAddress, r.contactEmail, r.specialRequests,
r.managerComments, r.confirmed, r.status, r.numpeople, r.reservedBy,
r.dateCreated, c.name, c.campID
FROM Reservation r, Camp c
WHERE r.campID=cid AND r.campID=c.campID
ORDER BY c.name, r.reservID
SELECT r.reservID, r.startdate, r.enddate, r.grouptype, r.stake, r.ward,
r.contactPhone, r.contactAddress, r.contactEmail, r.specialRequests,
r.managerComments, r.confirmed, r.status, r.numpeople, r.reservedBy,
r.dateCreated, c.name, c.campID
FROM Reservation r, Camp c
WHERE r.campID=c.campID
ORDER BY c.name, r.reservID
Tables: Campground and Campsite
SELECT c.groundID, c.groundName, s.siteID, s.siteName
FROM Campground c INNER JOIN Campsite s ON (c.groundid = s.groundID)
WHERE c.campID=cid
ORDER BY c.groundName, s.siteName
Tables: Campground, Campsite, and ReservationForCampsite
SELECT *
FROM ReservationForCampsite rfc, Campsite cs, Campground cg
WHERE rfc.reservID='rid' AND rfc.siteID=cs.siteID AND cs.groundID=cg.groundID
ORDER BY cg.groundName, cs.siteName
Tables: CampRule and CampRuleCategory
SELECT crc.category, cr.text
FROM CampRule cr LEFT JOIN CampRuleCategory crc ON (cr.catID=crc.catID)
WHERE crc.campID=cid
ORDER BY crc.campID, crc.category
SELECT DISTINCT category, crc.catID
FROM CampRuleCategory crc, CampRule cr
WHERE crc.catID=cr.catID AND campID=cid
Tables: Campsite and ReservationForCampsite
SELECT s.sitename FROM ReservationForCampsite r, Campsite s
WHERE r.reservID='rid' AND r.siteID=s.siteID
Tables: Reservation and Stake
SELECT * FROM Reservation r, Stake WHERE reservID='rid' AND r.stake=stake.stakeID
Tables: Reservation and ReservationForCampsite
SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
CAST(r.startDate AS DateTime) AS arrival,
CAST(r.endDate AS DateTime) AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE CAST(w.wardID AS NVARCHAR)=r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((CAST(r.startDate AS DateTime) >= 'firstDate' AND
CAST(r.startDate AS DateTime) <= 'lastDate') OR
(CAST(r.endDate AS DateTime) >= 'firstDate' AND
CAST(r.endDate AS DateTime) <= 'lastDate')) AND r.campID=cid AND rfc.siteID=sid
ORDER BY departure
Tables: Larger Joins
SELECT DISTINCT
rd.reservDate, g1.groundID,
(SELECT COUNT(*)
FROM Campsite
WHERE Campsite.groundID = g1.groundID
GROUP BY Campsite.groundID) -
(SELECT COUNT(*)
FROM ReservationForCampsite rfc2
INNER JOIN ReservationDate rd2 ON (rfc2.reservID=rd2.reservID)
INNER JOIN Campsite s2 ON (rfc2.siteID=s2.siteID)
WHERE rd2.reservDate=rd.reservDate AND g1.groundID=s2.groundID
) AS sitesOpen,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=rd.reservDate
AND cg5.groundID=g1.groundID AND r5.groupType='YW'
) AS countAdjacentYW,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=rd.reservDate
AND cg5.groundID=g1.groundID AND r5.groupType<>'YW'
) AS countAdjacentNonYW
FROM Campground g1
INNER JOIN Reservation r ON (r.campID=g1.campID)
INNER JOIN ReservationForCampsite rfc ON (r.reservID=rfc.reservID)
INNER JOIN Campsite s ON (s.groundID=g1.groundID)
INNER JOIN ReservationDate rd ON (rd.reservID=r.reservID)
WHERE g1.campID=cid
UNION
SELECT DISTINCT
r.startdate, g1.groundID,
(SELECT COUNT(*)
FROM Campsite
WHERE Campsite.groundID = g1.groundID
GROUP BY Campsite.groundID) -
(SELECT COUNT(*)
FROM ReservationForCampsite rfc2
INNER JOIN ReservationDate rd2 ON (rfc2.reservID=rd2.reservID)
INNER JOIN Campsite s2 ON (rfc2.siteID=s2.siteID)
WHERE rd2.reservDate=r.startdate AND g1.groundID=s2.groundID
) AS sitesOpen,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=r.startdate
AND cg5.groundID=g1.groundID AND r5.groupType='YW'
) AS countAdjacentYW,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=r.startdate
AND cg5.groundID=g1.groundID AND r5.groupType<>'YW'
) AS countAdjacentNonYW
FROM Campground g1
INNER JOIN Reservation r ON (r.campID=g1.campID)
INNER JOIN ReservationForCampsite rfc ON (r.reservID=rfc.reservID)
INNER JOIN Campsite s ON (s.groundID=g1.groundID)
WHERE g1.campID=cid
-- NOTE that the next two queries execute one after another in a loop for
-- all days in a chosen date range (could be day, week, month, year).
SELECT *
FROM ReservationForCampsite rfc, Campsite c, Reservation r, ReservationDate rd
WHERE rfc.siteID=c.siteID AND rfc.reservID=r.reservID AND c.siteID='sid'
AND rd.reservID=r.reservID AND rd.reservDate='date'
SELECT DISTINCT r.grouptype
FROM Campsite c
INNER JOIN Campground g ON (c.groundID=g.groundID)
INNER JOIN AdjacentCampground a ON (g.groundID=a.groundID)
INNER JOIN Campsite c2 ON (c2.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc ON (c2.siteID=rfc.siteID)
INNER JOIN reservation r ON (rfc.reservID=r.reservID)
INNER JOIN reservationdate rd ON (r.reservID=rd.reservID)
WHERE rd.reservDate='date' AND r.campID=cid AND c.siteID=sid
-- This second part picks up Saturday afternoon/evening reservations.
UNION
SELECT DISTINCT r.grouptype
FROM Campsite c
INNER JOIN Campground g ON (c.groundID=g.groundID)
INNER JOIN AdjacentCampground a ON (g.groundID=a.groundID)
INNER JOIN Campsite c2 ON (c2.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc ON (c2.siteID=rfc.siteID)
INNER JOIN reservation r ON (rfc.reservID=r.reservID)
WHERE r.startdate='date' AND r.campID=cid AND c.siteID=sid
Simple
Table: Activity
SELECT * FROM Activity WHERE campID='cid'
SELECT MAX(activityID) AS m FROM Activity
Table: Amenity
SELECT * FROM Amenity WHERE groundID=gid
SELECT * FROM Amenity WHERE groundID=gid ORDER BY amenityName
Table: Announcement
SELECT * FROM Announcement
WHERE campID <= 0 AND
(annBeginDate <= GETDATE() OR annBeginDate = '' OR annBeginDate IS NULL) AND
(annEndDate >= GETDATE() OR annEndDate = '' OR annEndDate IS NULL)
ORDER BY announceID DESC
SELECT * FROM Announcement
WHERE campID=cid AND
(annBeginDate <= GETDATE() OR annBeginDate = '' OR annBeginDate IS NULL) AND
(annEndDate >= GETDATE() OR annEndDate = '' OR annEndDate IS NULL)
ORDER BY announceID DESC
SELECT announceID, announceTitle, announceText,
CAST(MONTH(annBeginDate) AS varchar) + '/' +
CAST(DAY(annBeginDate) AS varchar) + '/' +
CAST(YEAR(annBeginDate) AS varchar) annBeginDate,
CAST(MONTH(annEndDate) AS varchar) + '/' +
CAST(DAY(annEndDate) AS varchar) + '/' +
CAST(YEAR(annEndDate) AS varchar) annEndDate
FROM Announcement
WHERE campID='cid'
Table: Camp
SELECT * FROM Camp ORDER BY name
SELECT * FROM Camp WHERE campID='cid'
SELECT COUNT(*) FROM Camp
SELECT allowYWSaturdays FROM Camp WHERE campID=cid
SELECT campID, name FROM Camp
SELECT campID, name FROM Camp ORDER BY name
SELECT campID, name FROM Camp WHERE campID='cid'
SELECT descrip, pic FROM Camp WHERE campID=cid
SELECT directionmap FROM Camp WHERE campID=cid
SELECT mappic FROM Camp WHERE campID=cid
SELECT name FROM Camp WHERE campID='cid'
SELECT name, contactEmail, campEmail FROM Camp WHERE campID=cid
SELECT firstScheduled, lastScheduled FROM Camp WHERE campID=cid
SELECT nextfirstScheduled, nextlastScheduled FROM Camp WHERE campID=cid
SELECT COUNT(name),
(SELECT COUNT(*) FROM Reservation WHERE confirmed<>'N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE confirmed='N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation) AS Total,
(SELECT COUNT(*) FROM UnavailableDate) AS Unavailable
FROM Camp
Complex
SELECT name, acceptReservations,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed<>'N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed='N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID) AS Total,
(SELECT COUNT(*) FROM UnavailableDate WHERE UnavailableDate.campID=c.campID) AS Unavailable,
(SELECT COUNT(*) FROM Campsite s, Campground g WHERE s.groundID=g.groundID AND g.campID=c.campID) AS Sites,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date0)) As Day0,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date1)) As Day1,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date2)) As Day2,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date3)) As Day3,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date4)) As Day4,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc
WHERE r.reservID=rfc.reservID AND r.startDate=r.endDate AND
r.campID=c.campID AND r.startDate IN (date5)) AS Day5
FROM Camp c
ORDER BY name
SELECT acceptReservations, nextacceptReservations,
CAST(MONTH(firstScheduled) AS VARCHAR) + '/' +
CAST(DAY(firstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(firstScheduled) AS VARCHAR) AS firstScheduled,
CAST(MONTH(lastScheduled) AS VARCHAR) + '/' +
CAST(DAY(lastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(lastScheduled) AS VARCHAR) AS lastScheduled,
allowYWSaturdays,
CAST(MONTH(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextfirstScheduled) AS VARCHAR) AS nextfirstScheduled,
CAST(MONTH(nextlastScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextlastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextlastScheduled) AS VARCHAR) AS nextlastScheduled,
currentSeason, nextSeason
FROM Camp
WHERE campID=cid
SELECT acceptReservations, nextacceptReservations,
CAST(MONTH(firstScheduled) AS VARCHAR) + '/' +
CAST(DAY(firstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(firstScheduled) AS VARCHAR) AS firstScheduled,
CAST(MONTH(lastScheduled) AS VARCHAR) + '/' +
CAST(DAY(lastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(lastScheduled) AS VARCHAR) AS lastScheduled,
allowYWSaturdays,
CAST(MONTH(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextfirstScheduled) AS VARCHAR) AS nextfirstScheduled,
CAST(MONTH(nextlastScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextlastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextlastScheduled) AS VARCHAR) AS nextlastScheduled,
allowYWSaturdays
FROM Camp
WHERE campID=cid
Table: Campground
SELECT * FROM Campground WHERE campID=cid ORDER BY groundName
SELECT * FROM Campground WHERE groundID=gid ORDER BY groundName
SELECT MAX(groundID) AS m FROM Campground
SELECT MIN(groundID) FROM Campground WHERE campID='cid'
SELECT groundID, groundName FROM Campground WHERE campID=cid ORDER BY groundName
Table: CampPicture
SELECT * FROM CampPicture WHERE campID='cid'
SELECT MAX(picID) AS m FROM CampPicture
SELECT picloc FROM CampPicture WHERE campID=cid
Table: CampRule
SELECT * FROM CampRule WHERE catID=cid
Table: CampRuleCategory
SELECT * FROM CampRuleCategory WHERE campID='cid'
SELECT MIN(catID) FROM CampRuleCategory WHERE campID='cid'
Table: Campsite
SELECT * FROM Campsite WHERE groundID='gid' ORDER BY siteName
SELECT COUNT(*) FROM Campsite WHERE groundID='gid'
SELECT siteCapacity FROM Campsite WHERE groundID=gid
SELECT siteCapacity FROM Campsite WHERE siteID='sid'
Table: Codes
SELECT code, description FROM Codes WHERE ctype='Activity' ORDER BY pos
SELECT code, description FROM Codes WHERE ctype='Priority' ORDER BY pos
SELECT pos FROM Codes WHERE ctype='Tracking' AND code='GA'
Table: Direction
SELECT * FROM Direction
SELECT * FROM Direction WHERE campID='cid'
Table: Facility
SELECT * FROM Facility WHERE campID='cid'
SELECT MAX(facilityID) AS m FROM Facility
Table: FAQ
SELECT * FROM FAQ ORDER BY pos
Table: Form
SELECT * FROM Form
Table: GeneralSite
SELECT * FROM GeneralSite
SELECT about, aboutpic, reserveInfo FROM GeneralSite
SELECT map FROM GeneralSite
Table: Glossary
SELECT * FROM Glossary ORDER BY term
Table: PrioritySchedule or NextPrioritySchedule
SELECT MAX(priorityallowed) FROM PrioritySchedule WHERE asOfDate < GETDATE()
SELECT MAX(priorityallowed) FROM NextPrioritySchedule WHERE asOfDate < GETDATE()
-- n is an integer between 1 and 6 inclusive
SELECT asOfDate FROM PrioritySchedule
WHERE priorityallowed='n' AND DATEPART(month,asOfDate) > 3 AND DATEPART(year, asOfDate) <= DATEPART(year,GETDATE())
SELECT asOfDate FROM NextPrioritySchedule
WHERE priorityallowed='n' AND DATEPART(month,asOfDate) > 3 AND DATEPART(year, asOfDate) <= DATEPART(year,GETDATE())
SELECT asOfDate FROM PrioritySchedule WHERE priorityAllowed=n
SELECT asOfDate FROM NextPrioritySchedule WHERE priorityAllowed=n
Table: Reservation
SELECT * FROM Reservation WHERE reservID=rid
SELECT MAX(reservID) FROM Reservation
SELECT contactEmail FROM Reservation WHERE reservID=rid
SELECT DISTINCT endDate FROM Reservation WHERE campID=id ORDER BY endDate
Table: ReservationDate
SELECT * FROM ReservationDate WHERE reservDate='date' AND reservID='rid'
SELECT reservID FROM ReservationDate WHERE reservDate='date'
SELECT reservDate FROM ReservationDate WHERE reservID='rid'
Table: ReservationForCampsite
SELECT * FROM ReservationForCampsite WHERE siteID='sid' AND reservID='rid'
Table: RuleCategory
SELECT * FROM RuleCategory
SELECT MIN(catID) FROM RuleCategory
Table: RuleTable
SELECT * FROM RuleTable
SELECT * FROM RuleTable WHERE catID='cid'
Table: Stake
SELECT stakeID, stakeName FROM Stake WHERE stakeID > 1 ORDER BY stakeName
SELECT stakeName FROM Stake WHERE stakeID=sid
Table: UnavailableDate
SELECT unavailDate FROM UnavailableDate WHERE campID=cid
SELECT unavailDate FROM UnavailableDate WHERE campID=cid ORDER BY unavailDate
Table: UserCamp
SELECT * FROM UserCamp WHERE userID='uid'
SELECT campID FROM UserCamp WHERE userID='uid'
Table: UserTable
SELECT * FROM UserTable WHERE userID='uid' AND password='password'
SELECT * FROM UserTable WHERE userID='uid'
SELECT userID, name FROM UserTable
Table: Ward
SELECT wardID, wardName, unittype FROM Ward WHERE stakeID='sid' ORDER BY wardName
SELECT wardName FROM Ward WHERE wardID=wid
Tables: AgentStake joined with others
SELECT AgentStake.*, Stake.stakeName
FROM AgentStake, Stake
WHERE campID=cid AND AgentStake.stakeID=Stake.stakeID
SELECT stakeName, c.name AS name, g.stakeID AS asi, s.stakeID AS si
FROM Stake s, Camp c, AssignedTo a, AgentStake g
WHERE s.stakeID=a.stakeID AND c.campID=a.campid AND g.campID=c.CampID
ORDER BY stakeName, Camp.name
SELECT stakeName, c.name AS name, g.stakeID AS asi, s.stakeID AS si
FROM Stake s, Camp c, AssignedTo a, AgentStake g
WHERE s.stakeID=a.stakeID AND c.campID=a.campid AND g.campID=c.CampID
ORDER BY c.name, stakeName
SELECT startDate, endDate, numPeople, groupType,
ward, reservedBy, r.campID,
r.contactPhone, contactAddress,
r.contactEmail, specialRequests,
managerComments, confirmed, totCost, c.name as propertyName,
managerName, c.contactPhone AS manPhone,
c.contactEmail as manEmail,
g.name AS spName, street, city, state, zip,
s.stakeName AS ssname, a.stakeName AS asname
FROM Reservation r, Camp c, AgentStake g, Stake s, Stake a
WHERE r.campID=c.campID AND c.campID=g.CampID AND
s.stakeID=stake AND a.stakeID=g.stakeID AND reservID=rid
Tables: AssignedTo and {Stake or Ward}
SELECT s.stakeID, stakeName
FROM AssignedTo a, Stake s
WHERE a.stakeID=s.stakeID AND a.campID='cid'
ORDER BY stakeName
SELECT wardID, wardName, unitType
FROM AssignedTo a, Ward w
WHERE w.stakeID=sid AND w.stakeID=a.stakeID AND a.campID=cid +
ORDER BY wardName
Tables: Camp, Campground, Campsite, and ReservationForCampsite
SELECT s.siteID, s.siteName, s.siteCapacity, g.groundName, r.reservID
FROM Campsite s
INNER JOIN Campground g ON (s.groundID=g.groundID)
INNER JOIN Camp c ON (g.campID=c.campID)
LEFT JOIN ReservationForCampsite r ON (r.reservID=" + editMode AND r.siteID=s.siteID)
WHERE c.campID=cid AND c.campID=g.campID AND g.groundID=s.groundID
ORDER BY g.groundName, s.siteName
Tables: Camp and Reservation
SELECT r.reservID, r.startdate, r.enddate, r.grouptype, r.stake, r.ward,
r.contactPhone, r.contactAddress, r.contactEmail, r.specialRequests,
r.managerComments, r.confirmed, r.status, r.numpeople, r.reservedBy,
r.dateCreated, c.name, c.campID
FROM Reservation r, Camp c
WHERE r.campID=cid AND r.campID=c.campID
ORDER BY c.name, r.reservID
SELECT r.reservID, r.startdate, r.enddate, r.grouptype, r.stake, r.ward,
r.contactPhone, r.contactAddress, r.contactEmail, r.specialRequests,
r.managerComments, r.confirmed, r.status, r.numpeople, r.reservedBy,
r.dateCreated, c.name, c.campID
FROM Reservation r, Camp c
WHERE r.campID=c.campID
ORDER BY c.name, r.reservID
Tables: Campground and Campsite
SELECT c.groundID, c.groundName, s.siteID, s.siteName
FROM Campground c INNER JOIN Campsite s ON (c.groundid = s.groundID)
WHERE c.campID=cid
ORDER BY c.groundName, s.siteName
Tables: Campground, Campsite, and ReservationForCampsite
SELECT *
FROM ReservationForCampsite rfc, Campsite cs, Campground cg
WHERE rfc.reservID='rid' AND rfc.siteID=cs.siteID AND cs.groundID=cg.groundID
ORDER BY cg.groundName, cs.siteName
Tables: CampRule and CampRuleCategory
SELECT crc.category, cr.text
FROM CampRule cr LEFT JOIN CampRuleCategory crc ON (cr.catID=crc.catID)
WHERE crc.campID=cid
ORDER BY crc.campID, crc.category
SELECT DISTINCT category, crc.catID
FROM CampRuleCategory crc, CampRule cr
WHERE crc.catID=cr.catID AND campID=cid
Tables: Campsite and ReservationForCampsite
SELECT s.sitename FROM ReservationForCampsite r, Campsite s
WHERE r.reservID='rid' AND r.siteID=s.siteID
Tables: Reservation and Stake
SELECT * FROM Reservation r, Stake WHERE reservID='rid' AND r.stake=stake.stakeID
Tables: Reservation and ReservationForCampsite
SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
CAST(r.startDate AS DateTime) AS arrival,
CAST(r.endDate AS DateTime) AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE CAST(w.wardID AS NVARCHAR)=r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((CAST(r.startDate AS DateTime) >= 'firstDate' AND
CAST(r.startDate AS DateTime) <= 'lastDate') OR
(CAST(r.endDate AS DateTime) >= 'firstDate' AND
CAST(r.endDate AS DateTime) <= 'lastDate')) AND r.campID=cid AND rfc.siteID=sid
ORDER BY departure
Tables: Larger Joins
SELECT DISTINCT
rd.reservDate, g1.groundID,
(SELECT COUNT(*)
FROM Campsite
WHERE Campsite.groundID = g1.groundID
GROUP BY Campsite.groundID) -
(SELECT COUNT(*)
FROM ReservationForCampsite rfc2
INNER JOIN ReservationDate rd2 ON (rfc2.reservID=rd2.reservID)
INNER JOIN Campsite s2 ON (rfc2.siteID=s2.siteID)
WHERE rd2.reservDate=rd.reservDate AND g1.groundID=s2.groundID
) AS sitesOpen,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=rd.reservDate
AND cg5.groundID=g1.groundID AND r5.groupType='YW'
) AS countAdjacentYW,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=rd.reservDate
AND cg5.groundID=g1.groundID AND r5.groupType<>'YW'
) AS countAdjacentNonYW
FROM Campground g1
INNER JOIN Reservation r ON (r.campID=g1.campID)
INNER JOIN ReservationForCampsite rfc ON (r.reservID=rfc.reservID)
INNER JOIN Campsite s ON (s.groundID=g1.groundID)
INNER JOIN ReservationDate rd ON (rd.reservID=r.reservID)
WHERE g1.campID=cid
UNION
SELECT DISTINCT
r.startdate, g1.groundID,
(SELECT COUNT(*)
FROM Campsite
WHERE Campsite.groundID = g1.groundID
GROUP BY Campsite.groundID) -
(SELECT COUNT(*)
FROM ReservationForCampsite rfc2
INNER JOIN ReservationDate rd2 ON (rfc2.reservID=rd2.reservID)
INNER JOIN Campsite s2 ON (rfc2.siteID=s2.siteID)
WHERE rd2.reservDate=r.startdate AND g1.groundID=s2.groundID
) AS sitesOpen,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=r.startdate
AND cg5.groundID=g1.groundID AND r5.groupType='YW'
) AS countAdjacentYW,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=r.startdate
AND cg5.groundID=g1.groundID AND r5.groupType<>'YW'
) AS countAdjacentNonYW
FROM Campground g1
INNER JOIN Reservation r ON (r.campID=g1.campID)
INNER JOIN ReservationForCampsite rfc ON (r.reservID=rfc.reservID)
INNER JOIN Campsite s ON (s.groundID=g1.groundID)
WHERE g1.campID=cid
-- NOTE that the next two queries execute one after another in a loop for
-- all days in a chosen date range (could be day, week, month, year).
SELECT *
FROM ReservationForCampsite rfc, Campsite c, Reservation r, ReservationDate rd
WHERE rfc.siteID=c.siteID AND rfc.reservID=r.reservID AND c.siteID='sid'
AND rd.reservID=r.reservID AND rd.reservDate='date'
SELECT DISTINCT r.grouptype
FROM Campsite c
INNER JOIN Campground g ON (c.groundID=g.groundID)
INNER JOIN AdjacentCampground a ON (g.groundID=a.groundID)
INNER JOIN Campsite c2 ON (c2.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc ON (c2.siteID=rfc.siteID)
INNER JOIN reservation r ON (rfc.reservID=r.reservID)
INNER JOIN reservationdate rd ON (r.reservID=rd.reservID)
WHERE rd.reservDate='date' AND r.campID=cid AND c.siteID=sid
-- This second part picks up Saturday afternoon/evening reservations.
UNION
SELECT DISTINCT r.grouptype
FROM Campsite c
INNER JOIN Campground g ON (c.groundID=g.groundID)
INNER JOIN AdjacentCampground a ON (g.groundID=a.groundID)
INNER JOIN Campsite c2 ON (c2.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc ON (c2.siteID=rfc.siteID)
INNER JOIN reservation r ON (rfc.reservID=r.reservID)
WHERE r.startdate='date' AND r.campID=cid AND c.siteID=sid
Thursday, April 12, 2007
USPS
This is my response to the New York Times article, "Online Booksellers Face Higher Costs for Shipping Abroad".
http://www.nytimes.com/2007/04/09/technology/09ecom.html?_r=2&adxnnl=1&oref=slogin&ref=technology&adxnnlx=1176411378-nWrBxxrAzIaU1Rvvp1xXaQ
This is a very interesting article. It seems the decision the US Postal Service made to not ship overseas for individuals is motivated by the need to reduce costs, and maybe make their operation more profitable. We hear a lot about how the world is getting smaller because of globalization. Technologies are getting better so communication and travel are faster, and people are more connected. But this decision from the USPS seems to be creating more distance.
The competitors of USPS are well-known, publicly traded companies, so maybe USPS is picking to stick with core competencies and drop aspects of the business that aren’t essential, or that are losing the organization money.
The Postal Reorganization Act of 1970 said that no post office will be closed for running a deficit. This was put in place to protect the postal workers, to ensure their jobs, but now causes major loses for the Postal Service. The postal service wasn’t created to make money, but to provide a service. But it wasn’t created to lose money either. Appropriate modifications must be made to achieve the organizational goals.
This definitely helps bigger companies, and creates major problems for smaller business. I wonder why the article focuses on bookstores because this will effect almost every type of business. I’m a believer of the free market, and that the invisible hand should rule. I feel bad for the families who own small businesses, but not bad enough to buy their more expensive books.
http://www.nytimes.com/2007/04/09/technology/09ecom.html?_r=2&adxnnl=1&oref=slogin&ref=technology&adxnnlx=1176411378-nWrBxxrAzIaU1Rvvp1xXaQ
This is a very interesting article. It seems the decision the US Postal Service made to not ship overseas for individuals is motivated by the need to reduce costs, and maybe make their operation more profitable. We hear a lot about how the world is getting smaller because of globalization. Technologies are getting better so communication and travel are faster, and people are more connected. But this decision from the USPS seems to be creating more distance.
The competitors of USPS are well-known, publicly traded companies, so maybe USPS is picking to stick with core competencies and drop aspects of the business that aren’t essential, or that are losing the organization money.
The Postal Reorganization Act of 1970 said that no post office will be closed for running a deficit. This was put in place to protect the postal workers, to ensure their jobs, but now causes major loses for the Postal Service. The postal service wasn’t created to make money, but to provide a service. But it wasn’t created to lose money either. Appropriate modifications must be made to achieve the organizational goals.
This definitely helps bigger companies, and creates major problems for smaller business. I wonder why the article focuses on bookstores because this will effect almost every type of business. I’m a believer of the free market, and that the invisible hand should rule. I feel bad for the families who own small businesses, but not bad enough to buy their more expensive books.
Subscribe to:
Posts (Atom)