Dean of Admission at DeAnza College has hired you to do the database design for

Question

Dean of Admission at DeAnza College has hired you to do the database design for a new registration system. That has

1. College has several departments  

2. Department offer several courses  

3. Courses have sections.  

4. College has locations (Room Sem 5 etc.)  

5. Students register for a section from a catalog (which is composed of Departments, Courses, Sections, Location.)  

6. Students have to pay for the course they take. You may want to think about resident, non resident.

Q1. Create Table and Tablespaces. Based on the crow natation above , create tables with constraints. Consider using the following constraints as appropriate Primary Key, Foreign key, Unique, Null or Check.

Q2. Project which tables will increase quickly and implement partitioning types in at least 2 tables in your schema. Explain why these partitioning types would be useful in the context of your implementation. :

Explanation / Answer

create table StudentRegistration
(
regId number(5) primary key,
StudentId number(5),
Feestatus varchar2(10) not null,
PaymentID number(3) references Payment
);

create table Section
(
SectionId number(4) primary key,
SectionNo number(3),
Courseid number(5) references Course,
LocId number(3) references Location,
starttime datetime,
endtime datetime,
startdate datetime,
enddate datetime,
userID number(4) references user
);

create table course
(Courseid number(5) primary key,
CourseName varchar2(10),
Prereq varchar2(10),
Deptid number(2) references Department,
SectionId number(5) references Section
);

create table Department
(Deptid number(2) primary key,
Name varchar2(20) not null,
Locid number(3) references Location,
CollegeId number(50) references College
)

create table Resident
(
Resident-Id number(5) primary key,
ResiName varchar2(20) not null,
Feeschedule varchar2(20),
userID number(4) references user
)

create table Location
(Locid number(3) primary key,
LocationName varchar2(20) not null

);

create table Payment
(PaymentID number(3) primary key,
PaymentName varchar2(20) not null,
regId number(5) references StudentRegistration
);
create table USER
( userID number(4) primary key
FirstName varchar2(50) not null,
LastName varchar2(50),
Address varchar2(100),
Telno number(10) not null,
Email varchar2(20),
ResidentId number(3),
Type varchar2(10),
regId varchar2(5)
);

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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