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&AuthType=shib&db=nlebk&AN=291311&site=eds-live&ebv=EB&ppid=pp_ii
- 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:
- Tables Created: create a table for each entity as noted in the ERD diagram in Step 1. 5 points
- Columns Created: create a column for each attribute as noted the ERD diagram in Step 1. 5 points
- Primary Key designation: primary key addressed for all tables with unique constraints specified as column properties. 5 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. 5 points
- Relationships Created, as noted in the ERD diagram in STEP 1. Relationships shall be enabled for referential integrity and cascade updates. 10 points.
- Data Added per row: 5 rows of data for each table. 10 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. 10 points
