Consider the following tables. Indicate whether the table is a relational table.
ID: 667213 • Letter: C
Question
Consider the following tables. Indicate whether the table is a relational table. Why or why not? If it is not a relational table, correct the table.
Table I (15 pts)
A (key)
B
C
2
400
1
44
721
2
51
256
3,4
Table I (15 pts)
A (key)
B
C
D
2
101
101
220
4
NULL
102
250
6
400
104
245
Table II
E (key)
A (refs table I)
1
2
3
4
5
4
7
NULL
Based on the Table I in 1 (b), answer the following questions. (20 pts)
Provide a super key that is also a key for table I.
Provide a super key that is not a key for table I.
Why it is invalid to ask for the first row in a relational table. (10 pts)
Give a relational database schema for a department-student database. Every department has a unique department number, a name and several contact phone numbers. Every student has a student id, a name (first and last), and a corresponding department. In the database schema, you should indicate the keys and foreign keys wherever applicable. (40 pts)
Note: Database schema includes the definition of the relation as well as all database constraints. For simplicity, the domain for each attribute can be omitted in the schema. The database schema should use the following format for each table:
Relation_name (attribute name list), PK: ..., FK (if any): ... refs ...
For example:
Project (ProjectNo, ProjectName), PK: ProjectNo.
Employee (EmployeeNo, Name, WorksOn), PK: EmployeeNo, FK: WorksOn refs Project.
A (key)
B
C
2
400
1
44
721
2
51
256
3,4
Explanation / Answer
1. B is the super key for table 1..
2. C is not a super key for table 1.
3. The schema is as below,
create table deparment
(
departmentNo int,
name varchar(255) NULL,
phoneNo varchar(255) NULL,
primary key (departmentNo)
);
create table student
(
studentId int,
firstName varchar(255) NULL,
secondName varchar(255) NULL,
int departmentId,
primary key (studentId),
foreign key (departmentId) references department(departmentNo)
);