Scribd
Upload
2 views2 pages

DB Assignment 3

The document outlines five distinct relational schemas for various systems: an academic system for COMSATS University Islamabad, a law firm database, a library management system for rare books, a faculty database, and a scheduling classes database. Each schema includes tables with primary and foreign keys, detailing the relationships and attributes relevant to their respective domains. The schemas cover entities such as persons, departments, cases, books, professors, and classrooms, among others.

Uploaded by

samiaahmedd16
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views2 pages

DB Assignment 3

The document outlines five distinct relational schemas for various systems: an academic system for COMSATS University Islamabad, a law firm database, a library management system for rare books, a faculty database, and a scheduling classes database. Each schema includes tables with primary and foreign keys, detailing the relationships and attributes relevant to their respective domains. The schemas cover entities such as persons, departments, cases, books, professors, and classrooms, among others.

Uploaded by

samiaahmedd16
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Problem-1: COMSATS University Islamabad Academic System

Relational Schema
Person(person_id PK, name, email, phone, dob)
Department(dept_id PK, name, contact, phone, establishment_year, dept_type)
Posting(person_id FK, dept_id FK, start_date, end_date, responsibilities)
PK: (person_id, dept_id, start_date)

Faculty(person_id FK PK, research_area, h_index)


AdminStaff(person_id FK PK, job_title, office_room)

Role(role_id PK, title, managing_dept_id FK)


PersonRole(person_id FK, role_id FK, posting_dept_id FK, start_date, end_date,
allowance)
PK: (person_id, role_id, start_date)

Problem-2 Data base for Law film


Relational Schema
Case(case_number PK, date_opened, date_closed, judgment_desc)
LegalEntity(entity_number PK, name, net_worth, entity_type)

Person(entity_number FK PK, ssn, address)


Organization(entity_number FK PK, tax_id, registration_no)

Plaintiff(case_number FK, entity_number FK, requested_judgment)


PK: (case_number, entity_number)

Defendant(case_number FK, entity_number FK)


PK: (case_number, entity_number)

Problem-3: Library Management System for Rare Books and Borrowers


Relational Schema
Book(book_id PK, title, author, pub_year, genre, book_category)
Antique(book_id FK PK, preservation_condition, estimated_value)
Rare(book_id FK PK, num_copies, signed_status)
SpecialEdition(book_id FK PK, illustrator, edition_details)

Borrower(borrower_id PK, name, address, membership_type)


Borrowing(book_id FK, borrower_id FK, borrow_date, due_date, return_status, fine)
PK: (book_id, borrower_id, borrow_date)

Exhibition(exhibition_id PK, name, start_date, end_date)


ExhibitionBook(exhibition_id FK, book_id FK)
PK: (exhibition_id, book_id)

Problem-4 Faculty Database


Relational Schema
Professor(ssn PK, name, age, rank, research_specialty)
GraduateStudent(ssn PK, name, age, degree_program, major_dept_no FK,
advisor_ssn FK)

Department(dept_no PK, dept_name, main_office, chairman_ssn FK)


Project(proj_no PK, sponsor_name, start_date, end_date, budget, pi_ssn FK)

WorkDept(prof_ssn FK, dept_no FK, time_percentage)


PK: (prof_ssn, dept_no)

WorksOnProject(prof_ssn FK, proj_no FK)


PK: (prof_ssn, proj_no)

StudentWork(student_ssn FK, proj_no FK, supervisor_ssn FK)


PK: (student_ssn, proj_no)

Problem-5 Scheduling ClassesS Database


Relational Schema
Room(building_id, room_no, capacity, room_type) PK: (building_id, room_no)
Classroom(building_id FK, room_no FK, board_type) PK: (building_id, room_no)
Lab(building_id FK, room_no FK) PK: (building_id, room_no)

Media(mtype_id PK, media_type, type_desc)


ComputerType(ctype_id PK, computer_type, type_desc, disk_capacity,
processor_speed)

Instructor(emp_id PK, name, rank, office_phone)


Timeslot(tsid PK, day_of_week, start_time, end_time)

Course(course_id PK, course_desc, credits)


Prerequisite(course_id FK, prereq_course_id FK) PK: (course_id, prereq_course_id)

Section(section_id PK, course_id FK, enrollment_limit)

Schedule(semester, year, section_id FK, room_building, room_no FK, timeslot_id


FK)
PK: (semester, year, section_id)

InstructorMedia(instructor_id FK, media_id FK) PK: (instructor_id, media_id)


RoomMedia(room_building, room_no FK, media_id FK) PK: (room_building,
room_no, media_id)
LabComputer(room_building, room_no FK, ctype_id FK) PK: (room_building,
room_no, ctype_id)
InstructorPrefTimeslot(instructor_id FK, timeslot_id FK) PK: (instructor_id,
timeslot_id)

You might also like