Category: Databases

Summary:
To apply all acquired knowledge in the last Project Assignments to create a Database Scheme. For this project, the ERD is provided to you.
Deliverables:
SQL Script with your name as follows: YourLastName_Project_Ph3.sql.
MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
ZIP file to include the two files mentioned above.
Required Software:
MySQL DBMS by Oracle. MySQL Community Server and MySQL Workbench.
Follow Step 1-3 for your Course Project Phase phase 1, to install the required software components.
Lab Steps:
Step 1: Analyze the ERD for a student information system provided below. Be alert for the specifications provided for: 1) Entities, 2) Attributes, 3) Primary Key, and 4) Relationships. You will need to have a clear understanding for these components to create your database using MySQL.
Step 2: Create a New Database in MySQL, Produce SQL File, Drop Table
Create a new Database, as you did in your Course Project Phase 1 deliverable and you already created a database using the MySQL DBMS. Reference the attached video (Course Project Ph1 Video.mp4), for a demonstration to create a new Database using MySQL. This video was initially presented to you for phase 2 of your project. Also, reference the supplemental text: Available for Free download as part of our UC Library. MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2. Link to UC Library: http://search.ebscohost.com/login.aspx?direct=true…
Utilize the SQL dialect you learned so far in this course for MySQL. Use the file supplemental textbook: MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill, as supplement your knowledge of the MySQL dialect.
Create your SCRIPT file to be named: YourLastName_Project_Ph3.sql.
Step 3: Include the following commands at the tops of your scripts:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CAMPUS;
DROP TABLE IF EXISTS ROOM;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS INSTRUCTOR;
DROP TABLE IF EXISTS APPROVED_INSTRUCTOR;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS STUDENT_GRADE;
SET FOREIGN_KEY_CHECKS=1;
Step 4: Create Tables in your database
Create a new Table for each of the entities provided to you in the ERD diagram in Step 1.
Add a column to represent each attribute addressed in the ERD diagram in Step 1.
Designate Primary Key, as noted in the ERD diagram in Step 1.
Designate a Foreign Key relationship between the tables, as noted in the ERD diagram in Step 1.
Enable referential integrity on the relationships, as needed.
Enable cascade updates as needed on the relationships.
Step 5: Designate Data Types. Update the data type as needed to enforce the domain constrain of the data. This needs to be completed for every column for all tables.
Dates: they should have a date data designation type.
Surrogate keys: shall be auto-numbered
Character type: shall have a character data designation type.
Step 6: Column Constraints Designation.
Grade designation must be of one of these values as follows: A, B, C, D, E, F, W, E (E=enrolled, and W = withdrawn).
Student’s first and last names are not to be designated as NULL.
Course Credit hours shall be BETWEEN one and four.
The instructor first and last name must NOT be NULL
Course name designation has to be UNIQUE and must not be NULL type.
Step 7: Data Table Addition
Use the INSERT operator to add minimum 2-3 rows of data per each database.
You are free to use any values you might like for each of the columns.
NOTE/ Reminder: you are required to add data to the parent table prior to adding any data to child tables, as referential integrity is enabled.
Step 8: Executing your SCRIPT
Must incorporate the COMMIT command at the end of your Script
Must incorporate the SHOW TABLES command, to display the table you created. At the end of the script created.
Must incorporate the SELECT statement to show data allocated for each table. This would be added at the end of the script.
EXECUTE your SCRIPT.
Copy and paste your OUTPUT into your MS Word file as follows: YourLastName_Project_Ph3_Output.doc
Step 9: Upload your work
Upload ZIP file to include the two files as follows: 1) SQL Script with your name as follows: YourLastName_Project_Ph3.sql, and 2) MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
Rubric:
NOTE: Please include good documentations.
Tables Created: create a table for each entity as noted in the ERD diagram in Step 1. 20 points
Columns Created: create a column for each attribute as noted the ERD diagram in Step 1. 20 points
Primary Key designation: primary key addressed for all tables with unique constraints specified as column properties. 20 points
Data Types: Addressed for each attribute. Should include: 1) Date data type incorporated, 2) Surrogate Key Automated, 3) Numeric data shall be numeric type, 4) Character data shall have a character type. 20 points
Relationships Created, as noted in the ERD diagram in STEP 1. Relationships shall be enabled for referential integrity and cascade updates. 40 points
Data Added per row: 5 rows of data for each table. 40 points
Column Constraints Added: As noted in the description for the project. Checked by adding rows with invalid values. Should take into consideration constraints as follows: 1) student name cannot be NULL, 2) course credit hours are restricted to allocations between 1-4, 3) course name is unique and NOT NULL, 4) instructor last name and first name can NOT be NULL, and 5) grade restrictions to the following values: A,B,C,D,F,I,W, E. 40 points

The data and fields file will be provided by your lecturer at the commencement of the class. The data relates to nutrition of breakfast cereal. Suppose that you are an Analytics team for a sport and dieting company. You have been given data on the nutritional components (e.g. sugar, fat, protein…) of common breakfast cereals and a rating. You wish to determine which cereals are best for diabetics and those on special diets. PLEASE TYPE OR SEND ME STEPS BY STEP IN THE WORD DOCUMENT HOW YOU GET THAT ANSWER. Upload the data into Excel for an initial check. The file is a csv (txt) file. Check for missing data and errors.
a. State the number of data records you have in the sample
b. You may notice some negative values which can be modified using an approximate based on a similar product and state your changes.
c. Provide any assumptions, changes and issues presented in the data that may impact your analysis
d. Sort the ratings from largest to smallest to get an idea of general customer approval of the cereals and state the cereal with the highest and lowest approval rating
e. Save the file as an Excel file for uploading into Power BI or Tableau.

This project is worth 14 marks and will be distributed as the following:
oDesign a Conceptual Schema using ER modeling concepts, including (Entities, Relationships, Attributes, Participation (Total or Partial), and Cardinality). (3 marks)
oTables before Normalization. (2 marks)
oTables after Normalization Using mapping Algorithm. (3 marks)
oUse MySQL or any other DBMS to create the normalized tables and
populate your tables with at least 10 rows. (3.5 marks)
oExecute the requested sample queries. (2.5 marks)
Each student must submit one report about their chosen Project via the Blackboard (Email submission will not be accepted and will be awarded ZERO marks) containing the following:
a)ER Diagram.
b)All schemas before normalization.
c)All schemas after normalization.
d)All SQL statements of:
§Creating tables.
§Inserting data in tables.
e)All requested queries/results.
Screenshots from MySQL (or any other software you use) of all the tables after population and query results.
You are advised to make your work clear and well presented; marks may be reduced for poor presentation. This includes filling in your information on the cover page.
You MUST show all your work, and text must not be converted into an image unless specified otherwise by the question.
Late submission will result in ZERO marks being awarded.
The work should be your own. Copying from students or other resources will result in ZERO marks.
Project I
Database System for a car rental company
Consider the company requirements as follows:
A car rental company needs a comprehensive database system to streamline its operations. The system should enable customers to rent cars from multiple locations (Riyadh, Jeddah, Makkah, Dammam, Al-Khobar, Qasim, Tabuk, AL-Baha, Al-Jawf, Ha’il, Aseer, Jazan, Najran). Customers will be required to register by providing their full name, email, phone number, and date of birth. The company owns a fleet of cars, which contains important details such as the manufacturer, model, manufacturing year, license plate, and daily rental rate. Every rental transaction will involve the customer, the rented car, and the rental period (start and end dates). As the company operates in multiple cities and states, multiple rental locations are available, each with its name, street address, city, state, and postal code.
Customers can rent multiple cars, and each car can be rented by multiple customers. A specific rental location will be associated with each car. Moreover, each rental transaction will be linked to a specific rental location. To maintain data integrity, constraints such as unique customer and car IDs will be enforced. Rental transactions must be associated with both a customer and a car.
On the other hand, each car should be linked to a rental location. Reports on rental transactions will be generated, including customer information, location, and period. User roles will be implemented for customers and system administrators. Customers will be able to view available cars, rent cars, and access their rental history. Meanwhile, administrators will be able to manage car inventory, rental locations, and customer accounts.
The system will ensure that cars are available for rent at specific locations and track car availability to prevent overbooking. Rental costs will be calculated based on the rental rate per day. User authentication and authorization will be implemented to protect customer data and system operations.
a)ER Diagram
b)Tables before the normalization
c)Tables after the normalization
d)Create the normalized tables and populate them with at least 10 rows
e)Write the sample requested Quesries & Execute them
1.List the first and last names of customers who have rented a car more than two times.
2.List all cars in Jazan whose manufacturing year is greater than 2007.
3.List all cars that have been rented from Jeddah city along with customer information (First and last name, email address, and phone number)
4.List all cars rented more than three times in all cities.
5.List all cars with a rental period of more than five days, along with the customer information (First and last name, email address, and phone number).