courseproject3.docx

Attached Files:

·  (220.793 MB)

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: 

· 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.