help please in database The second project task is to write DDL code to implemen

Question

help please in database

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 (i.e., 1^st, 2^nd, and 3^rd 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 already, but 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 name, last 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 it

Explanation / Answer

SQL statements are divied into 2 parts. One is DDL(Data definition Language) and another one is DML(Data Manipulation Language).
DDL:
   These statements are used to create and modify the structure of tables and objects. For example,
Create - Which is used to create a table and databse and its objects(triggers, index, view, store and procedure).
Alter - Which is used to change the structure of your existing tables/databses.
Drop - Used to delete the objects of your databse.
Comment - Used to add comment to the data dictonary.
Truncate - Used to empty the table.
Rename - Used to rename the objects.

There are a set of majors, each with an ID and name

DROP TABLE IF EXISTS major;
CREATE TABLE major(
   major_id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(100) NOT NULL,
   PRIMARY KEY ( major_id )
);

Here major is a table name, major_id is a primary key of major table and name is the major name.

Students have firstname, lastname, a year in school, a major and an student id
DROP TABLE IF EXISTS student;
CREATE TABLE student(
   student_id INT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(100) NOT NULL,
   lastname VARCHAR(100) NOT NULL,
   major_id INT NOT NULL,
   join_date TIMESTAMP,
   PRIMARY KEY ( student_id )
);

here student is a tbale name, student_id is a primary key of student table, firstname is student firstname name, lastname is student lastname, major_id is foreign key of student table, it holds the value of major table. join_date is student joined date.

Professors have firstname, last name and a faculty ID

DROP TABLE IF EXISTS professor;
CREATE TABLE professor(
   professor_id INT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(100) NOT NULL,
   lastname VARCHAR(100) NOT NULL,
   PRIMARY KEY ( professor_id )
);
Here professor is tbale name. professor_id is a faculty id and it is a primary key for professor table. firstname and lastname is professor name.

Classes have a name and classID

DROP TABLE IF EXISTS classes;
CREATE TABLE classes(
   classes_id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(100) NOT NULL,
   PRIMARY KEY ( classes_id )
);

Here classes is a table name, classes_id is a primary key of classes table and name is the classes name.

A veritey of classess are offered on a quartely basis, each taught by one professor and that class can have 1 or more students in them.
DROP TABLE IF EXISTS professor_class;
CREATE TABLE professor_class(
   professor_id INT NOT,
   student_id INT NOT NULL,
   classes_id INT NOT NULL,
   class_duration_date TIMESTAMP,
);
here a professor teach a more than one student in a class. class_duration_date used to denotes quartely basis.

Students receive a letter grade in every class.
DROP TABLE IF EXISTS student_grade;
CREATE TABLE student_grade(
   student_id INT NOT NULL,
   grade CHAR(2) NOT NULL,
   classes_id INT NOT NULL
);
student_id based on student table, classes_id based on classes table and grade provided every classes
Each major has a list of required classes.
In this case a major course has more than one classes. Example, in computer science engineering course has more than one subjects like Object Oriented Programming, Mathematical foundation of Computer Science, Applied Mathematics and etc...
so the table of classes can be altered like,
ALTER TABLE classes
ADD major_id INT NOT NULL,
FOREIGN KEY(major_id) REFERENCES major(major_id);

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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