02.
Relational Schema – examples
[PM Jat, DAIICT, Gandhinagar]
For all following database schema examples, try doing following
(1) Interpretation of each tuple in the relation
(2) What is Primary Key; validate that.
(3) What are Foreign Keys; what does each association, FK represents
(4) Any other constraints
#1 XIT Database
Here is complete schema of XIT database:
An instance XIT schema:
1 | Relational DB Schema Examples [pm jat @ daiict]
Student Relation
1. Each tuple represents a student entity
2. Primary Key is StudID
3. ProgID is Foreign Key referring to PID attribute of Program Relation. This foreign key
associates a student entity with a program entity; a value in this FK isID of the department
in which the student studies.
Program Relation
1. Each tuple represents a program entity
2. Primary Key is PID
3. Prog Name is constrained to be Unique and Not Null
4. DID is Foreign Key referring to DID attribute of Department Relation. This foreign key
associates a program entity with a department entity that is departmentthat offers the program.
A value in this FK is ID of the department in that offers the program
Department Relation
1. Each tuple represents a department entity
2. Primary Key is DID
3. Department Name is constrained to be Unique and Not Null
2 | Relational DB Schema Examples [pm jat @ daiict]
#2 Sales Database
3 | Relational DB Schema Examples [pm jat @ daiict]
Customer Relation
1. Each tuple represents a customer entity
2. CustNo is Primary Key
Item Relation
1. Each tuple represents an item entity
2. Primary Key is item “code”
Invoice Relation
1. Each tuple represents an invoice
2. Primary Key is “InvNo”
3. “CustNo” is a foreign key referring into customer table. This foreign key associates
the invoice with the customer (person who has ordered the items)
InvoiceDetails Relation
1. This relation is used to records details of an invoice. A tuple of this relationrepresents
an item entry in an invoice.
2. Primary Key: Compiste Key {invno, itemcode}
3. Two foreign keys here: InvNo, and “ItmCode”. Note these are two FKs and not acomposite.
4. FK “InvNo” refers to “InvNo” in invoice relation. The foreign key here associatesan entry
with a corresponding invoice.
5. FK “ItmCode” refers to “code” in item relation. The foreign key here associatesan entry
with a corresponding item.
4 | Relational DB Schema Examples [pm jat @ daiict]
#3 Company Database
Employee Relation
1. Each tuple represents an employee entity
2. Primary Key is “ENO”
3. “dno” is foreign key refers to “dno” in the department relation. This FK associatethe
employee with the department to which this employee works. A value in FK here is ID of
the department for the employee works.
4. “supe_eno” is another foreign key referring into employee table itself. This FK captures
supervision association; a value in this foreign is eno of the employee’s supervisor.
5 | Relational DB Schema Examples [pm jat @ daiict]
Department Relation
1. Each tuple represents a department entity
2. Primary Key is “DNO”
3. “mgr_eno” is foreign key refers to “eno” in the employee relation. A value in thisFK is
ENO of employee, who is manager of the department.
DLocation Relation
A department be located at multiple locations. We record name of all locations of a
department.
1. A tuple here records one location with reference to corresponding department
2. Primary Key is composite: {DNO, dlocation}
3. FK: dno having reference to corresponding department
Project Relation
1. Each tuple represents a Project entity
2. Primary Key is “PNO”
3. Each project is managed by some department. “DNO” is foreign key refers thatrefers to
the managing department.
6 | Relational DB Schema Examples [pm jat @ daiict]
WorksOn Relation
This relation records the fact of employees working on
different projects. An employee can work any number of
projects and a project can have many employees working on.
While we do this, we also record how many hours employee
works on aproject.
1. Every tuple here records one instance of an employee
working on a project alongwith the number of hours
2. Primary Key is composite: {EENO, PNO}
3. Two foreign keys: EENO, and PNO
4. FK refers to the ENO in to employee relation, where as
PNO refers to PNO ofproject relation.
Dependents Relation
This relation records all dependents of employees. An
employee may have multipledependents. We record few
details of dependents along with their names.
1. A tuple here records one dependent with reference to corresponding employee
2. Primary Key is composite: {EENO, dependent_name}
3. “eeno” is foreign key refers to “eno” in the employee relation.
7 | Relational DB Schema Examples [pm jat @ daiict]
#3 DA-Acad Database
We have following tables
Student(StudetID, StdName, ProgID, Batch)
Course(CourseNo, CourseName, Credit)
Faculty(FacultyID, FacultyName)
Offers(AcadYear, Semester, CourseNo, FacultyID)
Registers(StudetID, AcadYear, Semester, CourseNo, grade)
Result(StudetID, AcadYear, SemesterType, SPI, CPI)
Semester(AcadYear, SemesterType)
==> Observe Primary Keys here?
==> What are Foreign Keys here?
Identify what refers what in diagram below?
8 | Relational DB Schema Examples [pm jat @ daiict]
Identify what refers what in diagram below?
9 | Relational DB Schema Examples [pm jat @ daiict]
