2023年11月25日

数据库代写 | BUS 440 Database Management Project 1

本次数据库代写是根据场景确定实体、属性、用例图等，并根据三大范式进行调整

BUS 440 Database Management
Project 1

Instructions:
Part 1: Design the Database.

Step 1: Read the use case and carefully view the data. Write down the business rules.

Step 2: Define the entities, attributes, and relationships and model them.
To do so, write the relational schema. Look at the sample records and any data found inside the use case description. Find the possible themes, and then:
a. Setup the relational schema all of the attributes listed.
b. If possible, identify a candidate primary key that uniquely defines each row.
c. Create a dependency diagram.

Step 3: Normalize to third normal form (3NF).
1NF: “The key” Normalization to the first normal form (1NF) involves:
1. Looking at the sample records to find the primary key. We have already assigned a PK in the prior step.
2. Looking at the sample records to find any repeating groups.
3. Drawing the updated dependency diagram(s).

 

2NF “The Whole key”:
Normalization to the first second form (2NF) involves:
1. Being in 1NF.
2. Removing any partial dependencies.
Draw any updated relational schemas and dependency diagrams.

3NF “Nothing but the key”:
Normalization to the third normal form (3NF) involves:
1. Being in 2NF.
2. Containing no transitive dependencies.

Step 4: Improve the Design
Improving the design involves standardization of names, adding new attributes, adding new relationships and tables, etc.
1. Standardize names (e.g., Emp_Code vs. Emp_Num).
2. Add new attributes, like Vendor Street_Address, Vendor_City, Vendor_State, Vendor_Zip, Vendor_Email_Addresss, etc.)
3. Add any needed new relationships and tables.

Step 5: Build the ERD in Visio, LucidChart, or another similar tool.

Part 2: Build the Database in MySQL from the design and write SQL queries.
In this part, import the records into the tables and build the PKs and FKs in My SQL. Then, write the follow SQL programs to extract the data to answer these questions:
1) Which items are currently on order and from what vendor?
2) What is the list of inventory (items), along with the location (building and room) and the quantity on hand of each.
3) What is the aggregated count of items in each building? Each room of each building?
4) What is the manager name and phone number of each building and the products stored in that building?
5) What is the aggregated count of all items in all buildings by vendor name?
6) Write at least one more query of your choosing.