Consider the following GRADEBOOK Relational Schema describing the data for a gra
ID: 3913746 • Letter: C
Question
Consider the following GRADEBOOK Relational Schema describing the data for a grade book of an instructor.
Note: The attributes A, B, C, and D of COURSES store grade cutoffs.
Design an Oracle/MySQL database using SQL DDL based on the above relational schema. Below is a guideline.
-Create a user/schema
-Grant privileges to the user/schema
-Create tables (make appropriate assumptions. See below database state)
-Populate the tables (Insert data). Use the below database state
-Add foreign keys
Please put up all used SQL DDL statements.
CATALOG Cno, Ctitle) STUDENTS(Sid, Fname, Lname, Minit) COURSES(Term, Sec no, Cno, A, B, C, D) ENROLLS(Sid, Term, Sec no)Explanation / Answer
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; CREATE TABLE Catalog ( CNo char(30) NOT NULL, CTitle char(30) NOT NULL, PRIMARY KEY (CNo) ); CREATE TABLE Students ( SId BIGINT NOT NULL, FName char(30) NOT NULL, LName char(30) NOT NULL, MInit char(1), PRIMARY KEY (SId) ); CREATE TABLE Courses ( Term char(30) NOT NULL, Sec_No int NOT NULL, CNo char(30) NOT NULL, A int NOT NULL, B int NOT NULL, C int NOT NULL, D int NOT NULL, PRIMARY KEY (Term, Sec_No), FOREIGN KEY (CNo) REFERENCES Catalog(CNo) ); CREATE TABLE Enrolls ( SId BIGINT NOT NULL, Term char(30) NOT NULL, Sec_No int NOT NULL, PRIMARY KEY (SId, Term, Sec_No), FOREIGN KEY (SId) REFERENCES Students(SId), FOREIGN KEY (Term) REFERENCES Courses(Term), FOREIGN KEY (Sec_No) REFERENCES Courses(Sec_No) ); Insert into Catalog Values('CSCI130', 'Programming 1'), ('CSCI230', 'Programming 2'),('CSCI360', 'Database Management'); Insert into Students Values(123456789, 'Tom', 'Brady', 'P'), (333445555, 'Jerry', 'Seinfeld', 'A'),(9999999999, 'Doug', 'Heffernan', 'C'); Insert into Courses Values('Fall 2016', 1, 'CSCI130', 90, 80, 70, 60), ('Spr 2017', 1, 'CSCI230', 90, 80, 70, 60), ('Spr 2017', 2, 'CSCI360', 90, 80, 70, 60); Insert into Enrolls Values(123456789, 'Fall 2016', 1), (123456789, 'Spr 2017', 1), (333445555, 'Fall 2016', 1), (333445555, 'Spr 2017', 1),(333445555, 'Spr 2017', 2);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.