UTD CS 6360 - CS 6360 Database Design Term Project

Unformatted text preview:

CS 6360 Database Design Term ProjectProject DescriptionWonder Library is a library for all ages. Wonder Library would like one relational database to beable to smoothly carry out their work in an organized way. The library has following important modules: Person, Employee, Member, Books, Publishers, Authors and Payment.A Person can be an Employee or a Member. Employee can also be a Member. Details of a person such as Person ID, Name (First, Middle, Last), Address, Gender, Date of Birth, and Phone number (one person can have more than one phone number) are recorded. Employee must be at least 18 years old. The Person ID should have the format “PXXX” where X is a number from 0 to 9 (Hint: you can use regexp_like() function). Each member is issued a library card. The library card details such as card ID, date of issue,membership level (Silver or Gold) and other information are stored. Library sometimes may provide Promotions associate with library cards. Each Promotion includes a unique Promotion code, and its description. Employee can be one of three classes: Library Supervisors, Cataloging Managers or Receptionists. The start date of employment is recorded. Receptionist must be trained by a Trainer, a Trainer can be Library Supervisor or a Cataloging Manager. Library Supervisor and Cataloging Manager can train multiple Receptionists. Each member is classified as a Silver or Gold. A Guest log is maintained for the Gold members,which stores information such as member ID, guest ID, guest name, guest address, and guestcontact information. There are temporary IDs that a person gets when they visit as a guest of aGold member. Each guest ID is not unique in whole system, and only unique among all guest of a Gold member.Books details such as book ID, book title and other information are stored. Books are classified as 3 categories: Cate. 1, Cate. 2 and Cate. 3. Each Cataloging Manager is responsible for cataloging one category per day, but may catalog different categories at different days. Person can make comments to the Books. The comments include comment time, rating score (can be 1,2,3,4,5), and comment main contents. A publisher can publish more than one book, but a book is assumed to be published by a single publisher. The publisher details such as publisher ID and publisher name and other information (you can add assumptions) are stored. Author details such as author ID, author name and otherinformation is stored. One book can have multiple authors and one author can write more than one book.A receptionist maintains records of borrowing details. Borrowing details are stored containinginformation about the borrowed book, the date of issue and due date of return, the details about the person borrowing the book, details of the receptionist and payment detail. Borrowed details are stored only when a person borrows a book. Payment detail such as Payment ID, payment method (cash, debit/credit card), payment time and amount are stored. Project Questions1. Is the ability to model superclass/subclass relationships likely to be important in the Wonder Library management system like above? Why or why not?2. Can you think of 5 more rules (other than those explicitly described above) that are likely to be used in above environment? Please describe how your design would be changed to satisfy your additional rules?3. Justify using a Relational DBMS like Oracle for this project (Successfully design a relational database system, please show all the implementation in final report at Phase IV).Project ExercisesPhase I. Draw an EER to accurately represent this set of requirements. This will be your Conceptual Design. Clearly specify any assumptions that you are making. You can use any tools (software) to draw the EER but must use the symbols consistent with our textbook. The output of Phase I is EER diagram that satisfies the project description as much as possible. Some constrains may not be able to be specified in EER diagram, but please remember to go back to check the constraints in later phases to complete them. Phase II. Logical Database Design. It has been decided to use a relational DBMS to implement the database. Perform the following steps.a. Convert your Conceptual model (Phase I, feel free to change your conceptual model if needed and draw EER after your modifications) to an implementation data model that can be implemented in a relational DBMS like Oracle. During this process you replace M-N relationships and multi-valued attributes with constructs that can be implemented in the relational DBMS.b. Document your design in Database Schema format, explain how you obtained you schema.The output of Phase II is the schema of database derived from your EER design. Please indicate the primary keys and foreign keys of each relation.Phase III. Now, you are ready for implementation. Use appropriate naming conventions for all your tables and attributes.a. Normalize all your tables to third normal form.b. Draw a dependency diagram for each table from Phase III a.c. Write SQL statements to create database, tables and all other structures. Primary key and foreign keys must be defined as appropriate. Also specify data type and constraints for each attribute and in addition to specify the referential integrity. d. Use the Create View statement to create the following views:1. TopGoldMember - This view returns the First Name, Last Name and Date of membership enrollment of those members who have borrowed more than 5 books in past month.2. PopularBooks - This view returns the details of the most borrowed books over the past year.3. BestRatingPublisher – This view returns the names of publisher whose books are all have at least 4.0 average rating score. 4. PotentialGoldMember - This view returns the name, phone number and ID of the silver members who borrowed books in every month in the past year.5. PopularAuthor – This view returns details of authors whose books have been borrowed the most. e. Show the SQL statement of the following Queries. Feel free to use any of the views that you created in part (d.):1. List the details of all the supervisors of the library hired in past two months.2. Find the names of employees who are also a member and the books they have borrowed in the past month.3. Find the average number of books borrowed by the top five gold members in the library.4. Find the name of publishers and the title of the most popular book for each publisher.5. Find names of books that were

View Full Document

UTD CS 6360 - CS 6360 Database Design Term Project

Download CS 6360 Database Design Term Project
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...

Join to view CS 6360 Database Design Term Project and access 3M+ class-specific study document.

We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view CS 6360 Database Design Term Project 2 2 and access 3M+ class-specific study document.


By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?