2. Relational Concept and SQL (15 pts) SchClasses Courses C# Call# | C# 1001 I53

Question

2. Relational Concept and SQL (15 pts) SchClasses Courses C# Call# | C# 1001 I5300 1002 1S300 1003 15300 1004 15301 1005 1S301 1006 IS30 007 1S310 008 1S310 Section 1S380 1S300 S300 Intro to MIS 3 15301 Bus Comm 5310 Statistics IS380 Database S385 Systems Lee 1S380 15301 15380 IS310 IS385 IS300 1S385 15310 3 Jones 1 Lee 2 Smith Lee 1 Smith 2 Jones Assumptions: Each course may have many prerequisite courses. Each course may be required by many courses We may offer multiple sections of each course, each has a unique Calls and is taught by one instructor. An instructor may teach multiple classes Write SQL commands to create the above tables in sequence with proper primary key and foreign key declarations. (Note: You may decide column definitions as long as they are reasonable. You do not need to insert any records) Write SQL commands in sequence to delete the three tables. page 4 of 7

Explanation / Answer

//Creation of Tables

CREATE TABLE Courses(
'C#' VARCHAR(5) NOT NULL,
'CTitle' TEXT NOT NULL,
'CrHr' INT UNSIGNED NOT NULL,
PRIMARY KEY('C#'))

CREATE TABLE Prereq(
'C#' VARCHAR(5) NOT NULL,
'PreqC#' VARCHAR(5) NOT NULL,
Foreign key ('C#') references Courses('C#')
)

CREATE TABLE SchClasses(
'Call#' INT NOT NULL ,
'C#' VARCHAR (5) NOT NULL,
'Section' INT NOT NULL,
'Instructor' TEXT NOT NULL,
PRIMARY key ('Call#'),
Foreign Key ('C#') references Courses('C#')
)

// Deletion of Tables

Drop TABLE Prereq
Drop TABLE SchClasses
Drop TABLE Courses

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.