COMP1140-Assignment 3 Database and Information Management Solvced

This assignment has 3 parts as specified below.

1.              Revise the concept database design and logic database design, based on your revision and the feedback provided by your marker. I.e., do the following three steps:

1.1              Do concept database design, including Requirement Specification (including data requirements, transaction requirements, and business rules), and EER Diagram and Data Dictionary.

 

1.2              Map the EER model to the relational model. Document the relational schema in DBDL  

 

1.3              Normalize the relational schema to Boyce-Codd Normal Form (Point out what norm form each relation is in, do the normalisation if any relation is not already in BCNF.). The final normalised schema must be documented in DBDL.  

  

2.              Complete major steps in the physical database design (Please refer to the marking scheme for more detailed requirements)

2.1              Write SQL scripts that create the normalised SCS database, including all necessary tables with right parameters such as primary key, foreign key, default value.  

2.2              Write SQL statements satisfying the transaction requirements including:

2.2.1             Input proper data (as you consider legitimate) of at least three rows for every table, and

2.2.2             Implement the following queries (make sure to populate with enough and proper data into related tables so that non-void result is shown for each query. A query will be given zero mark if it has void output, or zero value, or no output). Note: in the queries below, the values of xxx, yyy, etc., can be the corresponding values in your database.  

 

Q1: Print the name of student(s) who has/have enrolled in the course with course id xxx.

Q2: Print the maximal number of speakers that the student with name xxx can borrow. The student is enrolled in the course with course id yyy. Note: speaker is a category.

Q3: For a staff member with id number xxx, print his/her name and phone number, the total number of acquisition requests and the total number of reservations that the staff had made in 2019.

Q4: Print the name(s) of the student member(s) who has/have borrowed the category with the name of camera, of which the model is xxx, in this year. Note: camera is a category, and model attribute must be in movable table.

Q5: Find the moveable resource that is the mostly loaned in current month. Print the resource id, resource name, and the membered if the member who loaned the resource .  

Q6: For each of the three days, including May 1, 2020, June 5, 2020 and September 19, 2020, print the date, the name of the room with name xxx, and the total number of reservations made for the room on each day.

3. Write a final report. The final report should include all the content of the above two tasks, including all the SQL scripts (The scripts are attached at the end of the report with small font like 8).

 

The final report should include the following:

1.      Reflection on your assignment 2 submission: briefly summarise your assignment 2 marker’s comments and suggestions, describe major places you will improve in this submission on assignment 2 content.

2.      Requirement Specification (including data requirements, transaction requirements, and business rules).  

3.      EER Diagram and Data Dictionary.

4.      Map the EER model to the relational model. Document the relational schema in DBDL. Give normalized relational schema in DBDL. Ensure that normalisation steps are shown. (Note: refer to assignment 2 specification for the requirements of this content).  

5.      SQL script (both in your sql file and in your report file) which creates the SCS database as stated in 2.1.  

6.      SQL statements (both in your sql file and in your report file) satisfying the transaction requirements as stated in 2.2

Method of submission: A softcopy submission is required:

•      zip all required files into one zip file (There are 3 required files at least: one is your wordformat report, another is Visio or other-format EER, and sql file that fulfills tasks 2.1 and 2.2. Note the sql file must be self-contained and can be imported to and run on another SQL Management Studio).  

•      The file name MUST be identified by 4 sections: A3, your first name, your surname, and your student number, e.g., A3SimonLee1234567.zip. It must be submitted to Blackboard -> Assessment -> AssignmentsSubmission-> Assignment3

•      In the report, you must have on the front a signed copy of the cover sheet (Assessment Item Cover Sheet – Individual) which is available from:                

http://www.newcastle.edu.au/__data/assets/pdf_file/0008/75383/AssessmentItemCoverSheet.pdf   

Note: please make sure to fill in your Tutorial Group (i.e., date/time), Tutor’s Name, as well as other items. Otherwise, your submission marking may be delayed.

Attention please: 

•      The SQL script part will be checked and marked in your registered Tut/Prac session of week 12. Therefore, you MUST be present at the tut/prac session (i.e., physically attend your lab if your lab is on campus; attend via Collaborate if your lab is online) for grading to receive marks.

•      Absence to your tut/prac session will result in being zero mark for “2. Complete major steps in the physical database design” section. (If you have special circumstances, you need to discuss with the lecturer in advance.)

Note: Ten percent of the possible maximum mark for the assessment item will be deducted for each day or part-day that the item is late.  Weekends count as one day in determining the penalty.