(14 points) Problem 6-34, p.283, where the beginning words “Write a database des

Question

(14 points) Problem 6-34, p.283, where the beginning words “Write a database description …” means “Write a SQL statement ...”

This problem requires you to create a database with six tables based on Figure 6-11 on p.283 (see the paragraph before this problem). The 6 tables are linked as: FACULTY-QAULIFIED-COURSE-SECTION-REGISTRATION-STUDENT

Note that CourseID in the SECTION table should be a foreign key and thus dash-underlined in the table.

Problems and Exercises Problems and Exercises 6-34 through 6-44 are based on the class schedul ing 3NF relations along with some sample data shown in Figure 6-11 Not shown in this figure are data for an ASSIGNMENT relation, which represents a many-to-many relationship between faculty and sections. Note that values of the SectionNo column do not repeat across semesters. StudentID (integer, primary key) StudentName (25 characters) FacultyID (integer, primary key) FacultyName (25 characters) CourseID (8 characters, primary key) CourseName (15 characters) DateQualified (date) SectionNo (integer, primary key) Semester (7 characters) 6-34. Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names, as needed for your SQL version). Assume the following attribute data types STUDENT (StudentlD, StudentName) QUALIFIED (FacultylD, CourselD, DateQualified) StudentID StudentName FacultylD CourselD DateQualified 38214 54907 66324 70542 2143 2143 3467 3467 4756 4756 ISM 3112 ISM 3113 ISM 4212 ISM 4930 ISM 3113 ISM 3112 9/2005 9/2005 9/2012 9/2013 9/2008 9/2008 er Altvater Aiken arra FACULTY (FacultylD, FacultyName) SECTION (SectionNo, Semester, CourselD) FacultylID FacultyName SectionNo er CourselD 2143 3467- 4756 Birkin Berndt Collins 2712 2713 2714 2715 1-2015 I-2015 1l-2015 11-2015 ISM 3113 ISM 3113 ISM 4212 ISM 4930 COURSE (CourselD, CourseName) REGISTRATION (StudentlD, SectionNo) CourselD CourseName StudentldD SectionNo

Explanation / Answer

--*******************************************************************
-- Database : DB2
-- Version : 9.7
-- Author : Chegg Expert
--********************************************************************
--=====================================
-- Creation of table SCHEMA_NAME.STUDENT
--=====================================

CREATE TABEL SCHEMA_NAME.STUDENT(StudentID INTEGER NOT NULL UNIQUE,StudentName VARCHAR2(25));
ALTER TABEL SCHEMA_NAME.STUDENT ADD CONSTRAINT PK_StudentID PRIMARY KEY(StudentID);

--=====================================
-- Creation of table SCHEMA_NAME.FACULTY
--=====================================

CREATE TABEL SCHEMA_NAME.FACULTY(FacultyID INTEGER NOT NULL UNIQUE,FacultyName VARCHAR2(25));
ALTER TABEL SCHEMA_NAME.FACULTY ADD CONSTRAINT PK_FacultyID PRIMARY KEY(FacultyID);

--=====================================
-- Creation of table SCHEMA_NAME.COURSE
--=====================================

CREATE TABEL SCHEMA_NAME.COURSE(CourseID VARCHAR2(8) NOT NULL UNIQUE,CourseName VARCHAR2(25));
ALTER TABEL SCHEMA_NAME.COURSE ADD CONSTRAINT PK_CourseID PRIMARY KEY(CourseID);

--=====================================
-- Creation of table SCHEMA_NAME.SECTION
--=====================================

CREATE TABEL SCHEMA_NAME.SECTION(SectionNo INTEGER NOT NULL UNIQUE,Semester VARCHAR2(7),CourseID VARCHAR2(8));
ALTER TABEL SCHEMA_NAME.SECTION ADD CONSTRAINT PK_SectionNo PRIMARY KEY(SectionNo);
ALTER TABEL SCHEMA_NAME.SECTION ADD CONSTRAINT FK1_CourseID FOREIGN KEY(CourseID) REFERENCES SCHEMA_NAME.COURSE(CourseID);

--=====================================
-- Creation of table SCHEMA_NAME.QUALIFIED
--=====================================

CREATE TABEL SCHEMA_NAME.QUALIFIED(FacultyId INTEGER NOT NULL UNIQUE,CourseID VARCHAR2(8) NOT NULL,DateQualified DATE);
--Composit primary key
ALTER TABEL SCHEMA_NAME.QUALIFIED ADD CONSTRAINT PK_composit_FacultyId_CourseID PRIMARY KEY(FacultyId,CourseID);
ALTER TABEL SCHEMA_NAME.QUALIFIED ADD CONSTRAINT FK1_FacultyId FOREIGN KEY(FacultyId) REFERENCES SCHEMA_NAME.FACULTY(FacultyId);
ALTER TABEL SCHEMA_NAME.QUALIFIED ADD CONSTRAINT FK2_CourseID FOREIGN KEY(CourseID) REFERENCES SCHEMA_NAME.COURSE(CourseID);

--=====================================
-- Creation of table SCHEMA_NAME.REGISTRATION
--=====================================

CREATE TABEL SCHEMA_NAME.REGISTRATION(StudentID INTEGER NOT NULL,SectionNo INTEGER NOT NULL);
--Composit primary key
ALTER TABEL SCHEMA_NAME.REGISTRATION ADD CONSTRAINT PK_composit_StudentID_SectionNo PRIMARY KEY(StudentID,SectionNo);
ALTER TABEL SCHEMA_NAME.REGISTRATION ADD CONSTRAINT FK1_StudentID FOREIGN KEY(StudentID) REFERENCES SCHEMA_NAME.STUDENT(StudentID);
ALTER TABEL SCHEMA_NAME.REGISTRATION ADD CONSTRAINT FK2_SectionNo FOREIGN KEY(SectionNo) REFERENCES SCHEMA_NAME.SECTION(SectionNo);

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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