The second project task is to write DDL code to implement a database design. One
ID: 3866924 • 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
drop table if exists professor;
create table professor(
id int not null auto_increment,
first varchar(100),
last varchar(100)
);
drop table if exists class ;
create table class(
name varchar(100),
id int not null auto_increment
);
drop table if exists student;
create table student(
id int not null auto_increment,
first varchar(100),
last varchar(100),
year int
);
drop table if exists major;
create table majot(
id int not null auto_increment,
name varchar(100)
);
drop table if exists teaches ;
create table teaches(
profid int,
classid int,
studentid int
);