CREATE Tables, Entities, Relationships, Keys and Structural constraints In a uni
ID: 3825719 • Letter: C
Question
CREATE Tables, Entities, Relationships, Keys and Structural constraints
In a university, we represent data about both students and employees. The university keeps track of each student's name, student number, social security number, address, phone, birth date, sex, class (freshman, sophomore,..., graduate), major department, minor department (if any), and degree program (B.A., B.S., M.A., M.S., ..., Ph.D.). Some user applications need to access the city, state, and zip code of the student's address and the student last name. Both social security number and student number have unique values for each student. Each student has a study plan that shows list of required courses to be taken. Each department is described by a name, department number, office number, office phone, and college. Both department name and department number have unique values for each department. Each department has a Chairperson or a Dean in charge of that department. Each course has a course name, course number, number of semester hours (credit), and offering department. Some courses have prerequisites (please pay attention here). Each course has the day, meeting time, place where the class is held. Each section has an instructor, semester, year, course, and section number. The section number distinguishes different sections of the same course that is taught during the same semester/year (may be at the same time), its values are 1, 2, 3, ..., up to the number of sections taught during each semester. Employees are classified into faculty and staff, both of them have dependents, the database stores the information of employees' dependents for the insurance and benefit purposes. Faculty could be full-time or part-time employees. Professors have ranks (Lecturer, Assistant Professor, Associate Professor, Full Professor) and salaries. Faculties (Professors) may hold different degree (highest degree is only considered here). Each professor belongs to at least one department. Professors may have joint appointments from other department(s). Staff are secretaries, program coordinators, assistant directors, directors, deans, vice presidents, and president. A grade report for a course has student names, section number, and grades. Students may have a transcript for all the courses they have taken. For graduate students, the student’s advisor should be included in the database.
CREATE Tables, Entities, Relationships, Keys and Structural constraints
Explanation / Answer
student_info
/*student's name, student number, social security number, address, phone, birth date, sex, class (freshman, sophomore,..., graduate), major department, minor department (if any), and degree program (B.A., B.S., M.A., M.S., ..., Ph.D.).]*/
CREATE TABLE student_info
(
stdNumber int NOT NULL PRIMARY KEY,
stdName varchar(255) NOT NULL,
stdSecurityNumber int NOT NULL,
address varchar(255),
city varchar(255),
phone varchar(32),
birthdate date,
sex varchar(50),
class varchar(23),
degree varchar(20),
majorDeptId int FOREIGN KEY REFERENCES department(deptId),
minorDept int FOREIGN KEY REFERENCES department(deptId),
empId int FOREIGN KEY REFERENCES employee(empId) /*student's advisor*/
);
department
CREATE TABLE department
(
deptId int NOT NULL PRIMARY KEY,
deptNumber varchar(255) NOT NULL,
officeNumber varchar(255) NOT NULL,
officePhone varchar(32),
college varchar(100)
);
department_incharge
CREATE TABLE department_incharge
(
deptInchargeId int NOT NULL PRIMARY KEY,
inchargeName varchar(255),
deptId int FOREIGN KEY REFERENCES department(deptId)
);
/*course name, course number, number of semester hours (credit), and offering department*/
CREATE TABLE course
(
courseId int NOT NULL PRIMARY KEY,
courseName varchar(255) NOT NULL,
courseNumber int NOT NULL,
semesterHourse date,
deptId int FOREIGN KEY REFERENCES department(deptId) /*offering department*/
);
course_meet_info
CREATE TABLE course_meet_info
(
cMeetId int NOT NULL PRIMARY KEY,
day date,
time date,
place varchar(255) NOT NULL,
courseId int FOREIGN KEY REFERENCES course(courseId)
);
/*instructor, semester, year, course, and section number*/
section
CREATE TABLE section
(
secNumber int NOT NULL PRIMARY KEY,
instructor varchar(255) NOT NULL,
semester varchar(30)
year date,
courseId int FOREIGN KEY REFERENCES course(courseId)
);
employee_type
CREATE TABLE employee_type
(
empTypeId int NOT NULL PRIMARY KEY,
typeName varchar(50) NOT NULL
); /*Here can delacre 1 is faculty and 2 is staff*/
employee_type_info
CREATE TABLE employee_type_info
(
empTypeId int FOREIGN KEY REFERENCES employee_type(empTypeId)
empPosition varchar(50) NOT NULL,
typeOfJob int not null /*1-full-time or 2-part-time*/
);/*based on emp type, the position has to be declared*/
employee
CREATE TABLE employee
(
empId int NOT NULL PRIMARY KEY,
empName varchar(255) NOT NULL,
phone varchar(32),
empTypeId int FOREIGN KEY REFERENCES employee_type(empTypeId),
deptId int FOREIGN KEY REFERENCES department(deptId),
);
grade
CREATE TABLE grade
(
gradeId int NOT NULL PRIMARY KEY,
grades varchar(10),
stdNumber int FOREIGN KEY REFERENCES student_info(stdNumber),
secNumber int FOREIGN KEY REFERENCES section(secNumber),
courseId int FOREIGN KEY REFERENCES course(courseId),
);