question archive 1)• Create ER diagram that will represent your database project for your target task
1)• Create ER diagram that will represent your database project for your target task
Subject:Computer SciencePrice: Bought3
1)• Create ER diagram that will represent your database project for your target task. Make sure you clearly show the entity, attribute, relationship between the entities, and specify the multiplicity. You also need to clearly specify the primary key, foreign key, or any other constrains if necessary.
• Create the tables that represented in your diagram. Use MySQL to create tables and insert data into each of the tables you create. Make sure that each table has at least 10 rows of data.
#create DB
DROP DATABASE IF EXISTS NYIT_Rating ;
CREATE DATABASE NYIT_Rating;
#select the DB
USE NYIT_Rating ;
#create the tables
CREATE TABLE department
(
deptNo VARCHAR(50) NOT NULL PRIMARY KEY,
deptName VARCHAR(50) NOT NULL
);
CREATE TABLE position
(
positionID VARCHAR(50) NOT NULL PRIMARY KEY,
positionName VARCHAR(50) NOT NULL
);
CREATE TABLE campus
(
campusID VARCHAR(50) NOT NULL PRIMARY KEY,
campusName VARCHAR(50) NOT NULL,
address VARCHAR(150) NOT NULL,
city VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
zipcode INT NOT NULL,
campusChairman VARCHAR(50) NOT NULL
);
CREATE TABLE courses
(
courseNo VARCHAR(50) NOT NULL PRIMARY KEY,
deptNo VARCHAR(50) NOT NULL,
courseName VARCHAR(50) NOT NULL,
departmentName VARCHAR(50) NOT NULL,
credits DOUBLE NOT NULL,
CONSTRAINT courses_pk_department
FOREIGN KEY (deptNo)
REFERENCES department(deptNo)
);
CREATE TABLE professor
(
professorID INT NOT NULL PRIMARY KEY,
deptNo VARCHAR(50) NOT NULL,
fName VARCHAR(50) NOT NULL,
lName VARCHAR(50) NOT NULL,
sex VARCHAR(1) NOT NULL,
positionID VARCHAR(50) NOT NULL,
positionName VARCHAR(50) NOT NULL,
campusID VARCHAR(50) NOT NULL,
courseNo VARCHAR(50) NOT NULL,
CONSTRAINT professor_fk_department
FOREIGN KEY (deptNo)
REFERENCES department (deptNo),
CONSTRAINT professor_fk_position
FOREIGN KEY (positionID)
REFERENCES position (positionID),
CONSTRAINT professor_fk_campus
FOREIGN KEY (campusID)
REFERENCES campus (campusID),
CONSTRAINT professor_fk_courses
FOREIGN KEY (courseNo)
REFERENCES courses (courseNo)
);
CREATE TABLE ratings
(
professorID INT NOT NULL PRIMARY KEY REFERENCES professor(professorID),
fName VARCHAR(50) NOT NULL,
lName VARCHAR(50) NOT NULL,
positionID VARCHAR(50) NOT NULL,
rating DOUBLE NOT NULL,
CONSTRAINT ratings_fk_position
FOREIGN KEY (positionID)
REFERENCES position (positionID)
);
INSERT INTO department Values
('CS0001', 'Computer Science'),
('EE0001', 'Electrical Engineering'),
('EET0001', 'Electrical Engineering Tech'),
('ECS0001' ,'Engineering Tech & Computer Science');
INSERT INTO position VALUES
('AAP01', 'Adjunct Assistant Professor'),
('AAP02', 'Adjunct Associate Professor'),
('AF01', 'Adjunct Faculty'),
('AI01', 'Adjunt Instructor'),
('D01', 'Department Chair'),
('P01', 'Assistant Professor'),
('P02', 'Associate Professor'),
('P03', 'Professor'),
('I01', 'Instructor'),
('L01', 'Laboratory Engineer'),
('SGPS01', 'Senior Graduate Programs Specialist'),
('S01', 'Senior Technician'),
('V01', 'Visiting Professor');
INSERT INTO campus VALUES
('MAN10023', 'Manhattan', '1855 Broadway', 'New York', 'New York', 'USA', 10023, 'Yoshikazu Saito'),
('OWB11568', 'Old Westbury', 'Northern Blvd & Valentines Ln', 'Old Westbury', 'New York', 'USA', 11568, 'Frank Lee'),
('NAJ210046', 'Nanjing', '9 Wenyuan Road', 'Nanjing', 'Jiangsu', 'China', 210046, 'Yoshikazu Saito');
INSERT INTO courses VALUES
('CSCI 101', 'CS0001', 'Computer Concept', 'Computer Science', 3.0),
('CSCI 105', 'CS0001', 'Introduction to Computational Tools', 'Computer Science', 2.0),
('CSCI 110', 'CS0001', 'Introduction to Computer Science', 'Computer Science', 3.0),
('CSCI 120', 'CS0001', 'Programming I', 'Computer Science', 3.0),
('CSCI 125', 'CS0001', 'Computer Programming I', 'Computer Science', 3.0),
('CSCI 130', 'CS0001', 'Computer Organization', 'Computer Science', 3.0),
('CSCI 135', 'CS0001', 'Digital Logic Design Fundamentals', 'Computer Science', 3.0),
