The second project task is to write DDL code to implement a database design. One
ID: 3867402 • Letter: T
Question
The second project task is to write DDL code to implement a database design. One systematic way of doing this is to create your schema initially based on your ER diagram and then, following rules of normalizations (ie, 1, 2nd, and 3rd Normal Form, as discussed in lecture), derive the additional tables needed. Note that for some of you, these choices may come more intuitively, even though you almost certainly are still leveraging normal forms implicitly. We will go over an example transformation from ER to DDL in class, so you have an example. (We have gone over some alreadybut a reminder will be helpful for you) Regardless, the goal of this assignment is to write an SQL script that creates the DDL necessary to implement the schema described in the first part of the project Please turn in an SQL file (can just be a text file) which has the necessary DDL statements that would allow me to rebuild the schema, even if the schema already exists. Make sure you include support for all attributes and entities. For example, your DDL should look something like: DROP TABLE IF EXISTS major; CREATE TABLE major ( id int not null auto_increment, ... (other major attributes) . . . .. (other tables) .. and so forth. Remember that the rules of the schema are: • There are a set of Majors, each with an ID (numeric) and a name • Students have first name, last name, a year in school, a major, and an student ID (numeric) • Professors have first namelast name, and a faculty ID (numeric) • Classes have a name and a class ID (numeric) A variety of classes are offered on a quarterly basis, each taught by one Professor and that class can have 1 or more students in them • Students receive a letter grade in every class • Each Major has a list of required Classes for itExplanation / Answer
Hi,
Please find below the answer-
Ans 2 - DDL Scripts -
1. CREATE TABLE PROFESSOR(FACULTYID NUMBER PRIMARY KEY, FIRSTNAME VARCHAR(20),LASTNAME VARCHAR(20));
2. CREATE TABLE MAJOR(MAJORNAME VARCHAR (10),MAJORID NUMBER PRIMARY KEY);
3. CREATE TABLE STUDENT(STUDENTID NUMBER PRIMARY KEY,FIRSTNAME VARCHAR(10),LASTNAME VARCHAR (10),YEAR_IN_SCHOOL NUMBER(5),MAJORID VARCHAR(10) FOREIGN KEY REFERENCES MAJOR(MAJORID));
4. CREATE TABLE CLASS(CLASSID NUMBER PRIMARY KEY,CLASSNAME VARCHAR(20));
5. CREATE TABLE ENROLL(STUDENTID NUMBER FOREIGN KEY REFERENCES STUDENT(STUDENTID),CLASSID NUMBER FOREIGN KEY REFERENCES CLASS(CLASSID),GRADE VARCHAR(2));
6. CREATE TABLE CLASS_SCHEDULE(FACULTYID NUMBER FOREIGN KEY REFERENCES PROFESSOR(FACULTYID),CLASSID NUMBER FOREIGN KEY REFERENCES CLASS(CLASSID),NO_OF_STUDENTS NUMBER(4));
Ans 3 - Writing Queries
Select * from PROFESSOR;
Select * from MAJOR;
Select * from STUDENT;
Select * from CLASS;