CMSC4003

Lab Assignment2: ER Model

Name:

Due: See the due date in WebCT calendar.

  1. ER Diagram

Draw an ER diagram for the following entity types and add at least 10 relationship types of your own for these entity types in the ER diagram. For simplicity, you do not need to mark the attributes of each entity in the ER diagram. You should focus on definingcorrectrelationshiptypes as well as their cardinality ratios and participation constraints.

Student (SId, SSNo, StuName, CurAddress, LocalTel, PermAddress, PermTel, Sex, BDate, Gpa), PK: SId.

Department (DId, DeptName, DeptAddress, Tel), PK: DId.

DegreeProgram (PId, ProgName, ProgType, UnivReq, CollReq, DeptReq), PK: PId.

CourseDescription (CNo, Title, Credits, Description), PK: CNo.

CourseOffering (SeqId, Semester, Year), PK: SeqId.

TextBook (ISBN, TextName, Publisher), PK: ISBN.

Faculty (FacSSNo, FacName, OfficeAddress, Tel), PK: FacSSNo.

Attach your ER diagram with your lab assignment submission.

  1. Try primary key and foreign key constraints

SQL Document:

SQL Quick Reference:

In this lab assignment, you will run SQL commands from a file.

(a) Create a file called lab2_try.sql. Put the following commands into the file.

drop table student;

create table student(

sid number(10) constraint pk_student primary key,

stuname varchar2(30),

localtel number(10));

insert into student values (12, 'a', 1);

insert into student values (12, 'b', 1);

(b) Now open another PuTTY window and log into SQL*Plus.

(c) Type start lab2_try or @lab2_try

Why do you see the error message in the output?

Answer:

Give the name of the constraints that was violated.

Answer:

(d) Go back to the editor, correct the problem and run the file lab2_try.sqlin SQL*Plusagain.

(e)Go back to the editor and addthe following commands to the file lab2_try.sql.

drop table StudentCourse;

Note that the above statement must be added at the beginning of the file. Due to the referential relationship between tables Student and StudentCourse, StudentCourse must be dropped before Student is dropped.

Append the following statements to the end of the file lab2_try.sql.

create table studentcourse(

sid number(10),

cid number(8),

grade number(3,1),

constraint pk_studentcourse primary key (sid, cid),

constraint fk_sid_student foreign key (sid) references student(sid));

insert into studentcourse values(12, 54,3.5);

insert into studentcourse values(412, 54,3.5);

(f) Save the file and run it in SQL*Plus

Why do you see the error message in the output?

Answer:

Give the name of the constraints that was violated.

Answer:

(g) Go back to the editor, correct the problem and run the file lab2_try.sql in SQL*Plus again.

  1. Now consider the ER diagram that you created in part 1 of the lab. Create a file lab2_er.sqlthat contains SQL commands to create tables for ONLY the entity types (NOT the relationship types) in the ER diagram. Decide the data types of each attribute by yourself. Make sure that the tables can be created in SQL*Plus. Leave the file in your account for grading purpose.

Print out the filelab2_er.sql and submit it along with this lab assignment.

1