Based on the following requirements for a University database, please write crea

Question

Based on the following requirements for a University database, please write create statements for 5 tables. Please do not use the instructor table and the department table. If you make any further assumptions, please specify those assumptions. Please make sure to identify primary keys, foreign keys and any other possible constraints on the tables. The university is organized into departments. Each department is identified by a unique name (dept name), is located in a particular building, and has a budget. Each department has a list of courses it offers. Each course has associated with it a courseid, title, dept name, and credits, and may also have associated prerequisites. Instructors are identified by their unique ID. Each instructor has name, associated department dept name), and salary Students are identified by their unique ID. Each student has a name, an associated major department (dept name), and tot cred (total credit hours the student earned thus far). . The university maintains a list of classrooms, specifying the name of the building, room number, and room capacity. The university maintains a list of all classes (sections) taught. Each section is identified by a course id, sec id, year, and semester, and has associated with it a semester, year, building, room number, and time slot id (the time slot when the class meets) The department has a list of teaching assignments specifying, for each instructor, the sections the instructor is teaching. . The university has a list of all student course registrations, specifying, for each student, the courses and the associated sections that the student has taken (registered for).

Explanation / Answer

The following tables are created for the above database as per the sql server syntax. You may skip the tables you already have in your database.

1. create table department
(
dept_name varchar(30) primary key,
--dept_id int primary key,
building_name varchar(30),
budget float check (budget>0)
);

2. create table course
(
course_id int primary key,
dept_id int foreign key references   department(dept_name),
title varchar(30) not null,
credits int,
prerequisites varchar(30)
);

3. create table instructor
(
id int primary key,
name varchar(40) not null,
dept_name varchar(30) foreign key references department(dept_name),
salary float not null check(salary>0)
);

4. create table student
(
sid int primary key,
name varchar(40) not null,
major varchar(30) foreign key references   department(dept_name),
tot_cred not null,
);

5. create table classroom
(
building_name varchar(40) not null,
room_number int primary key,
room_capacity int check(room_capacity >0)
);

6. create table class
(
course_id int foreign key references   course(course_id),
sec_id int primary key,
year int not null,
semester int not null,
building_name varchar(40) not null,
room_no int foreign key references classroom(room_number),
slot_id int
);

7. create table assignment
(
instructor_id int foreign key references instructor(id),
assignment_id int primary key,
name varchar(30) not null,
class_id int foreign key references class(sec_id)
);

8. create table registration
(
stud_id int foreign key references student(sid),
course_id int foreign key references course(course_id),
sec_id int foreign key references class(sec_id)
);

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

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