Question

A database at a college is required to support the following requirements. Complete the information-level design for this set of requirements. Use your own experience to determine any constraints you need that are not stated in the problem. Represent the answer in DBDL.

a. For a department, store its number and name.

b. For an advisor, store his or her number and name and the number of the department to which he or she is assigned.

c. For a course, store its code and description (for example, MTH110 or Algebra).

d. For a student, store his or her number and name. For each course the student has taken, store the course code, course description, and grade received. In addition, store the number and name of the stu- dent?s advisor. Assume that an advisor may advise any number of students but that each student has just one advisor.

Answer 1

a. DEPARTMENT(Number, Name,Loc)

b. ADVISER(ID, Name, Dep_Num, Loc)

c. COURSE(Code, Desc, Loc)

d. STUDENT(ID, Name, Code, Desc, AID, AName, Grade,Loc)

ADVISER(AID, AName, Dep_Num, Loc)

COURSE(Code, Desc, Loc)

Explanation:

a.

To store a department number and name; the following syntax is needed.

The database name is written in uppercase (Preferred) while column names are written in lower case.

Let DEPARTMENT represent the name of the table

Let Number and Name represent the department number and department name.

The syntax goes thus;

DEPARTMENT(Number, Name,Loc)

b.

We'll follow the same logic used in (a) above.

Let ADVISER represent the table name

Let AID, AName and Dep_Num represent the advisor number, name and number of department assigned to the advisor, respectively.

The syntax goes thus;

ADVISER(AID, AName, Dep_Num, Loc)

c.

We'll follow the same logic used in (a) above.

Let COURSE represent the table name

Let Code and Desc represent the Course Code and Course Description.

The syntax goes thus;

COURSE(Code, Desc, Loc)

d.

Here, we'll need 3 tables.

The first is the student table (that'll be created now).. while the other two tables are (b) and (c) above.

For a student, we need to store his or her number and name.

For each course the student has taken, store the course code, course description, and grade receive.

So, the table definition is (using the same logic)

STUDENT(ID, Name, Code, Desc, AID, AName, Grade,Loc)

ADVISER(AID, AName, Dep_Num, Loc)

COURSE(Code, Desc, Loc)