ummary:To administer all acquired ability in the aftermost Activity Assignments to actualize a Database Scheme. For this project, the ERD is provided to you.Deliverables:
SQL Calligraphy with your name as follows: YourLastName_Project_Ph3.sql.
MS Word Document with your OUTPUT, called as follows: YourLastName_Project_Ph3_Output.doc.
ZIP book to accommodate the two files mentioned above.
MySQL DBMS by Oracle. MySQL Community Server and MySQL Workbench.
Follow Step 1-3 for your Advance Activity Appearance appearance 1, to install the appropriate software components.
Lab Steps:Step 1: Analyze the ERD for a apprentice advice system provided below. Be active for the blueprint provided for: 1) Entities, 2) Attributes, 3) Primary Key, and 4) Relationships. You will charge to accept a bright compassionate for these apparatus to actualize your database using MySQL.
Step 2: Create a New Database in MySQL, Produce SQL File, Drop Table
Actualize a new Database, as you did in your Advance Activity Appearance 1 deliverable and you already created a database application the MySQL DBMS. Advertence the absorbed video (Course Activity Ph1 Video.mp4), for a affirmation to actualize a new Database using MySQL. This video was initially presented to you for appearance 2 of your project. Also, advertence the supplemental text: Available for Chargeless download as allotment 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 accent you abstruse so far in this advance for MySQL. Use the book added textbook: MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill, as supplement your knowledge of the MySQL dialect.
Actualize your SCRIPT book to be named: YourLastName_Project_Ph3.sql.
Step 3: Include the afterward commands at the acme of your scripts:
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;
Step 4: Create Tables in your database
Actualize a new Table for anniversary of the entities provided to you in the ERD diagram in Step 1.
Add a cavalcade to represent anniversary aspect addressed in the ERD diagram in Step 1.
Designate Primary Key, as acclaimed in the ERD diagram in Step 1.
Designate a Foreign Key accord amid the tables, as acclaimed in the ERD diagram in Step 1.
Enable referential candor on the relationships, as needed.
Enable avalanche updates as bare on the relationships.
Step 5: Designate Abstracts Types. Update the abstracts blazon as bare to accomplish the area constrain of the data. This needs to be completed for every cavalcade for all tables.
Dates: they should accept a date abstracts appellation type.
Surrogate keys: shall be auto-numbered
Appearance type: shall accept a appearance abstracts appellation type.
Step 6: Column Constraints Designation.
Brand appellation charge be of one of these ethics as follows: A, B, C, D, E, F, W, E (E=enrolled, and W = withdrawn).
Student's aboriginal and aftermost names are not to be appointed as NULL.
Advance Acclaim hours shall be BETWEEN one and four.
The adviser aboriginal and aftermost name charge NOT be NULL
Advance name appellation has to be UNIQUE and charge not be NULL type.
Step 7: Data Table Addition
Use the INSERT abettor to add minimum 2-3 rows of abstracts per anniversary database.
You are chargeless to use any ethics you ability like for anniversary of the columns.
NOTE/ Reminder: you are appropriate to add abstracts to the ancestor table above-mentioned to abacus any abstracts to adolescent tables, as referential candor is enabled.
Step 8: Executing your SCRIPT
Charge absorb the COMMIT command at the end of your Script
Charge absorb the SHOW TABLES command, to affectation the table you created. At the end of the calligraphy created.
Charge absorb the SELECT account to appearance abstracts allocated for anniversary table. This would be added at the end of the script.
EXECUTE your SCRIPT.
Copy and adhesive your OUTPUT into your MS Word book as follows: YourLastName_Project_Ph3_Output.doc
Step 9: Upload your work
Upload ZIP book to accommodate the two files as follows: 1) SQL Calligraphy with your name as follows: YourLastName_Project_Ph3.sql, and 2) MS Word Document with your OUTPUT, called as follows: YourLastName_Project_Ph3_Output.doc.
Tables Created: actualize a table for anniversary article as acclaimed in the ERD diagram in Step 1. 20 points
Columns Created: actualize a cavalcade for anniversary aspect as acclaimed the ERD diagram in Step 1. 20 points
Primary Key designation: primary key addressed for all tables with different constraints defined as cavalcade properties. 20 points
Abstracts Types: Addressed for anniversary attribute. Should include: 1) Date abstracts blazon incorporated, 2) Surrogate Key Automated, 3) Numeric abstracts shall be numeric type, 4) Appearance abstracts shall accept a appearance type. 20 points
Relationships Created, as acclaimed in the ERD diagram in STEP 1. Relationships shall be enabled for referential candor and avalanche updates. 40 points
Abstracts Added per row: 5 rows of abstracts for anniversary table. 40 points
Cavalcade Constraints Added: As acclaimed in the description for the project. Checked by abacus rows with invalid values. Should booty into application constraints as follows: 1) apprentice name cannot be NULL, 2) advance acclaim hours are belted to allocations amid 1-4, 3) advance name is different and NOT NULL, 4) adviser aftermost name and aboriginal name can NOT be NULL, and 5) brand restrictions to the afterward values: A,B,C,D,F,I,W, E. 40 points
Order a unique copy of this paper