Build a Distributed Database Solution that interacts with TRAMS
Description:
You have to build another complete database solution that interacts (and eventually links) to the TRAMS database. This database can be something like an employee payroll and accounting system, a housekeeping scheduling and timekeeping system, an inventory property control system, a restaurant food service system, or anything else you may think of to "expand" the TRAMS database. You will just need to "loosely tie" your completely independent database to the TRAMS database via one or more tables (mainly the Property Table and/or the Unit Tables). All concepts will require instructor approval prior to beginning, and may even require changes to the class approved TRAMS Database, so you and "link" your tables between databases.
Milestone 1 Details:
In this first milestone, you will start, (note: this is a WORK IN PROGRESS) to:
1) Define the tentative expansion project (which can be in Oracle, MySQL, or SQL Server).
Your "business" and this database is completely separate and independent, but "loosely" tied to the Property (or Units) in TRAMS.
Here are some examples or ideas to get you started (feel free to steal and expand on these
ideas)....
Golf Packages and Tee-Time Booking
As a separate database from TRAMS, this system may include extensive capabilities for booking golf packages and tee-times, that are tied to a property or location near the property. For example, several guests at the Grand Oasis would like the ability to book golf packages that include golfing at over 100 golf courses in the Myrtle Beach area. A sample package (which could be charged to an active room reservation) may include one round per person per day at any medium quality golf course in the area. For an additional charge, which is different for each course, the guest can play other higher quality courses. The system could also track:
o Which courses are included in the package
o The unique price for each higher quality course for each different season of each course
o Pricing for golf cart/equipment rentals
o How many rounds were actually played by each guest (did they exceed the package limits?)
o Tee Times available for each course (Booking times vary by the number of people in the group)
o Cancellation fees and/or discounts
o The system could also charge directly to the guest folio in TRAMS.
More? It's YOUR database! Keep it separate from TRAMS but "loosely" tied to the property.
Reservation Availability Linked a Housekeeping Scheduling and WorkOrder System
TRAMS could be linked to a Housekeeping Scheduling System. For example, employees could be linked to a particular property. Employees would have work schedules for different pay periods, with clock in and clock out capabilities, vacation, and tax information to help with producing payroll and determining who is available to work each week. Furthermore, TRAMS could be fully integrated with Work Orders and Asset Management. For example, if the head housekeeping determines that a Unit is damaged and must be out of service for the next few days, a work order can be generated that removes the Unit from availability for the estimated repair period.
RV Park with Electric Meter Billing & Other Owner Charges
TRAMS could be expanded to include RV and Campsites on a property. The separate system automatically determines site availability based on the length of the RV, and amenities requested (like full hook ups). It integrates with a lot of TRAMS's functionality but adds a new folio billing and payment system for site rentals each day (short term reservations) or monthly. Additional charges can be automatically added for items such as electric usage, internet connectivity, etc. Electric meter readings can be stored in the system, and various reports show electric usage and costs. Discounts can also apply.
Room Service, and in house food services:
A property could have one of more independent food service establishments that need to keep track of their inventory, menus and pricing, and (perhaps) reservations. These food services may integrate with TRAMS by allowing an "Active" (not prior) guest to charge their bills as "Room service" to their guest folio and/or receive a discount for dining at the establishment. Some food and beverage items may even be "age restricted" - to prevent alcohol from being delivered via room service...or may have "daily special" pricing for a particular day of the week.
1) Choose a scope for the application domain that you will represent and manage its data in your project throughout the course, and describe your application, by identifying interesting and unique features of it. Your application is expected to be of “reasonable size”, i.e... have a total of at least 6 MAJOR, but no more than 15 entity sets plus relationships.
2) Construct the entity relationship diagram for your application by specifying entities, relationships, attributes, keys, cardinality of relationships, data types, constraints, etc. just like you did for TRAMS.
3) Establish business rules for your system (make up some crazy rules so you'll have something to program against later). Anything dealing with Status Changes, numeric calculations, or "data processing" make good business rules that you'll be able to program against later (that's why the TRAMS scenario is so crazy - not realistic in some situations). Crazy is okay - it's your business (and rules).
4) For the meantime, Include a COPY of the TRAMS table(s) you'll connect to in your own system (we will eventually connect to the LIVE version of those same tables - and eventually remove them from your system). Remember, your application is completely independent from TRAMS, and should stand on its own.
5) This system may be PHYSICALLY ON ANY database platform (Oracle, MySQL, or SQL Server). In fact I encourage you consider experimenting with writing a script for another platform to compare the differences. Don't worry, we'll connect the two databases seamlessly via XML at the end of the semester.
This will be a work in progress! After turning in milestone one, keep asking yourself these questions, and document changes along the way:
a) Are there any flaws in the relational database schema you used?
b) Are there opportunities to combine relations without introducing redundancy? If so, indicate which, and if not, tell me there are none.
c) Are the tables in 3rd Normal Form?
For the meantime, here is what is required for Milestone 1
1. An initial write, description and specific (bulleted) business rules of your TRAMS expansion system you are designing. Your name should appear on this and every page of all subsequent attachments.
2. An E-R diagram (saved as a .jpg image), with no fewer than 6 (and no more than 15) major entity sets, that shows the design of your database. This design should be compatible with your description of how the database will be used, and should "connect" to one (or more) of the tables in the class approved TRAMS System (which you may SLIGHTLY modify, if necessary, for your business fit). On your diagram display a copy of the TRAMS table you will connect with (although you will not enforce a foreign key constraint between databases). You just show the TRAMS table(s) on your ERD so we know it's the "gateway" between databases.
3. The T-SQL script that is used to create your database and tables. You do not need to have sample data yet...but you will by the next milestone.
4. Put both items (Business Write up with Rules - with an image of the Logical Diagram included, and your Physical database Script (for Oracle, SQL Server, or MySql) saved in .txt format, in a single zip file).
Milestone 2 Details:
In MS 2, you will prepare your documentation and coding outline...
The Final Database Document you turn in will end up being a SINGLE MS Word Document or Simple HTML Webpage (preferred) with Sections (described below) containing Hyperlinks to open up "snapshots" of the individual sprocs, udfs, and/or triggers you will be writing. They will be displayed as separate individual text files (instead of one continuous script). You can still have your a full continuous script, but the documentation should make it easy to read about each thing you will program, and actually open up the portion of the code that the documentation is relevant for.
To start MS 2, first, make changes based on instructor feedback from Milestone 1 (if necessary), and start organizing your document as follows...
Part 1. Project Overview. Based on your initial write up and feedback from MS 1, you may need to revise your scenario text / summary of the intent of your final project. If the initial write up scenario was good and contained enough summary content, then just paste it again here. If not, edit it and review. Part I should be a general overview of one to two paragraphs. Move your business rules to Part 2 (below). You will only program against certain business rules you deem necessary.
Part 2. Data Specifications Overview (work in progress).
Part A: List specific BUSINESS RULES Satisfied by the ERD and normal DB Constraints (PK, FK, DK, CK). Copy and paste your E-R diagram and embed it here, but also include the image of the ERD as a separate file with your zipped submission.
Part B: List specific BUSINESS RULES Satisfied by TRIGGERS and outlines for each trigger you plan to program
- Trigger Name (will eventually be hyperlinked to the real trigger.txt file you will write. You will have one txt file for each trigger)
Part C: List specific BUSINESS RULES Satisfied by STORED PROCEDURES and outlines for each SPROC you plan to program
- SPROC Name (will eventually be hyperlinked to the real spoc.txt file you will write. You will have one txt file for each sproc)
- DETAILED Description of what the sproc should do. Does it call others? Does it have a cursor?
Does it have error handling routines? Does it utilize a linked server or impact other distributed databases/tables?
Part D: List specific BUSINESS RULES Satisfied by USER DEFINED FUNCTIONS and outlines for each UDF you plan to program
-UDF Name
-DETAILED Description of what the UDF should do. Does it call others? Does it have a cursor?
Does it have error handling routines? Does it utilize a linked server or impact other distributed databases/tables?
Part 3. OTHER SCRIPTS (not already hyperlinked in Part I of documentation): As you develop them, save your scripts SEPARATELY (in .txt files) including, but not limited to (a) DB and Table Creation Script with data relationships and constraints (PK and FK constraints) including, (b) INSERT INTO statements sample data, (c) events (stored procedures and User Defined Functions), actions (triggers) of the database, and (d) a test/demo script (which you will present in a 15 minute video at the end of the course) running through everything showing it works. I don't expect a majority of the scripts to have been written by the time you turn in MS2, but they should be defined. In other words, if you do not have a particular Stored Procedure, Function, and/or Trigger, finalized, then include placeholders (a description) of what you intend to write/develop. Keep copies of old scripts, so they can be included under "archived materials".
Final Turn in Details (NOTE: you may not have all the information to do all this for MS2....this is just a head's up of what's expected at the end of the semester).
1) You will run your database on your LOCAL Machine. You will need as much sample data as possible to properly demonstrate your final project. Have some variety of data!
2) You must have at least 2 (4 for teams) DISTRIBUTED queries (that use a linked server to YOUR TRAMS database on TITAN).
3) You must have at least 6 (9 for teams) or more Stored Procedures and or User Defined Functions. These Procedures or UDFS must be fairly complex and unique from one another. At least 2 (3 for teams) Procedures should demonstrate the use of Cursors.
4) You must have at least 4 (6 for teams) Triggers. These Triggers should be documented, with comments, and be fairly complex. An extra (5th) trigger should be included to enforce referential integrity across multiple databases.