The following is a database design about students, who may major and minor in zero or more areas, where each area belongs to one school. The following ER diagram shows the idea:

Clearly, all together 5 tables will be needed. The following are the SQL statements to create some of these 5 tables:

CREATE TABLE School (

Id INTEGER PRIMARY KEY,

Name VARCHAR(45) UNIQUE NOT NULL

);

CREATE TABLE Area (

Id INTEGER PRIMARY KEY,

Name VARCHAR(25) UNIQUE NOT NULL,

School INTEGER NOT NULL

REFERENCES School(id) ON DELETE CASCADE

);

CREATE TABLE Majors (

Student INTEGER

REFERENCES Student(Id) ON DELETE CASCADE,

Area INTEGER REFERENCES Area(Id),

PRIMARY KEY(Student,Area)

);

CREATE TABLE Student (

Id INTEGER PRIMARY KEY,

Name VARCHAR(30) NOT NULL,

Age INTEGER NOT NULL,

Gender CHAR(1) NOT NULL

CHECK(Gender IN ('M','F'))

);

CREATE TABLE Minors (

Student INTEGER

REFERENCES Student(Id) ON DELETE CASCADE,

Area INTEGER REFERENCES Area(Id),

PRIMARY KEY(Student,Area)

);

1. (8 pts) write SQL statements to insert the following records into school table:

school id = 1, school name = “computing and software engineering”

school id = 2, school name = “basket weaving and juggling”

INSERT INTO School (Id, Name) VALUES ( 1, ‘computing and software engineering’);

INSERT INTO School (Id, Name) VALUES (2, ‘basket weaving and juggling’);

2. (12 pts) write SQL statements to insert the following records into area table:

area id = 1, area name is “computer science”, which belongs to school id = 1

area id = 2, area name is “software engineering”, which belongs to school id = 1

area id = 3, area name is “information technology”, which belongs to school id = 1.

INSERT INTO Area (Id, Name, School) VALUES ( 1, ‘computer science’, 1);

INSERT INTO Area (Id, Name, School) VALUES ( 2, ‘software engineering’, 1);

INSERT INTO Area (Id, Name, School) VALUES ( 3, ‘information technology’, 1);

3. (16 pts) write SQL statements to show the name of the students who are majoring in the area with id 1.

SELECT S.Name

 FROM Student S JOIN Majors M ON (M.Student=S.Id)

 WHERE M.Area =1;

4. (20 pts) write SQL statements to show id and name of each student, with the number of areas they are minoring on.

SELECT S.Id, S.Name, COUNT (M.Area)

FROM Student S JOIN Minors Ma ON (M.Area=S.Id)

GROUP BY S.Name, S.Id;

Homepage