Concept database design and logic database design

Assignment Help Database Management System

Reference no: EM133678871

Assignment: SCS Resource Management Database Project - Physical Database Design

In this assignment, steps in the physical database design are conducted (as described below) as well as revising the conceptual database design and logical database design of assignment 1 and assignment 2, and a final report of the project is written.

This assignment has 3 parts as specified below.
Note:
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:

Do conceptual database design, including Requirement Specification (including data requirements, transaction requirements and business rules), and EER Diagram and Data Dictionary.
Map the solution EER model to the relational model. Document the relational schema in DBDL
Normalize the relational schema to Boyce-Codd Normal Form (Point out what norm form each relation is in, and do the normalisation if any relation is not already in BCNF.). The final normalised schema must be documented in DBDL.
Complete major steps in the physical database design (Please refer to the marking scheme for more detailed requirements).

Note: If your code cannot be run through successfully by the marker (including executing all the submitted code as a whole, and re-running the code to start the whole code again by clicking "Execute"), you will get zero marks for section 2. So it is strongly suggested that you try your code with a different account and on a different computer to make sure it will work at the marker's end. (Note: to meet this requirement, it is essential that the codes for dropping all tables are included and work.)

Write SQL scripts that create the normalised SCS Resource Management database, including all necessary tables with the right parameters such as primary key, foreign key, default value.
Note: in creating foreign keys (FK), at least 5 FKs for all the tables should use "ON UPDATE CASCADE, ON DELETE CASCADE". Otherwise, marks will be deducted.

Write SQL statements satisfying the transaction requirements including:

Input proper data (as you consider legitimate) of at least five (5) rows for every table, and
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 no output). Note: in the queries, the values of ttt, xxx, yyy, zzz, 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 reservations that the staff had made in 2022. 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, model attribute must be in movable table, and "this year" must be decided by the system.
Q5: Find the moveable resource that is the mostly loaned in current year. Print the resource id and resource name. Note: "current year" must be decided by the system.
Q6: For each of the three days, including May 1, 2024, June 5, 2024 and September 19, 2024, print the date, the name of the room, and the total number of reservations made for the room xxx on each day.

Write a final report. The final report should include all the content of the above two tasks, including all the SQL scripts.

The final report should include the following:
Reflection on your assignment 2 submission: briefly summarise your assignment 2 marker's comments and suggestions, and describe major places you will improve in this submission on assignment 2 content.
Requirement Specification (including data requirements, transaction requirements and business rules).
EER Diagram and Data Dictionary
Map the EER model to the relational model. Document the relational schema in DBDL. Give normalised relational schema in DBDL. Ensure that normalisation steps are shown. (Note: refer to assignment 2 specification for the requirements of this content).
SQL script (both in your sql file and in your Word report file) which creates the Numberone Pizza database as stated in 2.1.
SQL statements (both in your sql file and in your Word report file) satisfying the transaction requirements as stated in 2.2.

Reference no: EM133678871

Questions Cloud

Compliant with sarbanes oxley and hippa : Initek is fully compliant with Sarbanes Oxley and HIPPA all the controls are in place and functioning as designed.

Identify two nursing interventions to incorporate in a plan : Identify two nursing interventions to incorporate in a plan of care within this culture and/or religion. Provide a rationale for each chosen intervention.

Provides stem as management approach : What does STEM stand for? Explain how you would use STEM (all four steps) if you were a culinary manager to prevent any outbreaks

Analyze the ways in which interprofessional aspects : Analyze the ways in which interprofessional aspects of the policy will support efficient and effective achievement of desired outcomes for the target population

Concept database design and logic database design : INFO6001 Database Management, University of Newcastle - steps in the physical database design are conducted (as described below) as well as revising the concept

Rapidly growing city is outgrowing its facility : A bank in a rapidly growing city is outgrowing its facility. The bank officers feel that the operations part of the bank should be moved

Describes specific goals that should be established : Describes specific goals that should be established to address a selected health care problem. Ensures that the goals are realistic, measurable and attainable.

Preparation verification illumination incubation validation : Aria is making a decision and has suddenly become aware of a new idea, preparation verification illumination incubation validation

Describe priorities that a care coordinator would establish : Describe priorities that a care coordinator would establish when discussing the plan with a patient and family member, making changes based upon evidence.

Reviews

len3678871

4/18/2024 10:33:13 PM

Online Assignment Information Please doNOT submit the zip file, instead, please submit 2 files - a Word file for the report, and an .sql file for the SQL code. zip all required files into one zip file (basically 2 files - one is your word-format report, the other is Visio or other-format EER). The file name MUST be identified by 4 sections: A3, your first name, your surname, and your student number, e.g., A3SimonLee1234567.zip Submit your softcopy in this directory. In your submission, In the report file, you must have on the front a signed copy of the cover sheet (Assessment Item Cover Sheet – Individual) which is available i am sending you the question and the answers which I submitted make sure he uses this EER file

Write a Review

Required(*) Message

User Account

All Pages