Reference no: EM133118706
ICTDBS506 Design Database - WIN College
Assessment Task - Create EER Logical Model
Simulated Workplace Case Study -Library System
Consider the following description for a Library Information System:
A library needs to keep records of current loans of copies of books to borrowers and reservations for book titles.
Each borrower is identified by a BORROWER# and each copy of a book title in the library is identified by an ACCESS# as the library may have more than one copy of a given book title.
The NAME and ADDRESS of each borrower is held so that communications such as overdue loan reminders can be sent when necessary.
The information required about book titles is TITLE, AUTHORS, PUBLISHERS, PUBLICATION DATE, and ISBN.
For individual copies of a book title the PURCHASE PRICE and CURRENT PRICE are required, where purchase price is the price the library paid at the time of purchase and current price is the current listed price charged to borrowers who lose books. ISBN is an internationally used identifier for a book title.
A borrower may have any number of books out on loan at any one time, but a book may be borrowed by only one borrower at a time.
The DUE DATE for each loan is recorded so that overdue notices can be sent out. Book titles out on loan may be reserved by other borrowers pending their return.
Borrowers may make a reservation for only one book title at a time. Reservations for a book title are processed in order of the date the reservation was made as copies are returned.
You can use a MySQL Database - MAMP (which is universal for Apple and PC) as well as use Workbench to create a EER diagram.
Task - Create EER Logical Model
1. Identify and report on attributes and determine data types that are required for your database ensuring that you undertake normalisation of the attributes.
2. Develop an EER/Logical Model Design using Workbench for the Library system and clarify cardinality of relationships. Workbench can be used to create the physical model.
3. Document your attributes, normalised data and EER diagram
4. Submit to your Trainer to seek feedback and obtain approval. Make changes according to feedback - if required.
5. Confirm primary and foreign keys according to database/technical requirements analysis from A2.
6. Identify referential integrity constraints and organisational business rules.
7. Establish database management system constraints and incorporate into database design using Workbench software.
8. Design and develop data validation rules, indexes.
9. Document database design according to organisational policies and procedures.
10. Design database according to organisational requirements - this will be created from within Workbench from the ERD model created in this assessment.
11. Forward Engineer the Model in Workbench into .sql file to backup Database. Save to your computer.
12. Import your database into MAMP.
13. Incorporate data into your database e.g., user information and other information from your findings in A2.
14. Download the data Dictionary and submit with this assessment task.
Specifications
What you have to Submit/Upload;
Task 1
1. Report on the attributes and the data types and normalisation of attributes that are required for your database - This Assessment Task
Task 2
2. Create your EER Logical Data Model
3. Obtain Feedback for ERD Diagram from Trainer and make changes according to feedback comments.
4. Identify Referential Integrity restraints and business rules - This Assessment Task.
5. Data Dictionary
Task 3
6. Answers to these all the questions uploaded to A3 in Moodle/Learn under A3 Assessment Submission
Attachment:- Create EER Logical Model.rar