Class Activity DatabaseSystemLab
Hasnain Azhar f2024065433 W04
Activity 1: Library Management System
Entities:
Books, Members, BorrowRecords
Tables
Books:
Attribute Data Type Constraint BookID INT PK, NOT NULL Title VARCHAR(100) NOT
NULL Author VARCHAR(100) NOT NULL Publisher VARCHAR(100)
Year INT
Members:
Attribute Data Type Constraint MemberID INT PK, NOT NULL Name VARCHAR(100)
NOT NULL Email VARCHAR(100) UNIQUE Phone VARCHAR(20)
BorrowRecords:
Attribute Data Type Constraint BorrowID INT PK, NOT NULL
MemberID INT FK BookID INT FK BorrowDate DATE NOT NULL ReturnDate DATE
Foreign Keys
MemberID → Members(MemberID)
BookID → Books(BookID)
Relationships
Members → BorrowRecords : One-to-Many
Books → BorrowRecords : One-to-Many
Activity 2: Student Course Registration
SystemEntities:
Students, Courses, Teachers, Enrollment
Tables
Students:
Attribute Data Type Constraint StudentID INT PK, NOT NULL Name VARCHAR(100) NOT
NULL Email VARCHAR(100) UNIQUE
Major VARCHAR(50)
Teachers:
Attribute Data Type Constraint TeacherID INT PK, NOT NULL Name VARCHAR(100) NOT
NULL Department VARCHAR(50)
Courses:
Attribute Data Type Constraint CourseID INT PK, NOT NULL Title VARCHAR(100) NOT NULL
Credits INT
TeacherID INT FK Enrollment:
Attribute Data Type Constraint EnrollmentID INT PK, NOT NULL StudentID INT FK
CourseID INT FK Grade VARCHAR(2)
Foreign Keys
TeacherID → Teachers(TeacherID)
StudentID → Students(StudentID)
CourseID → Courses(CourseID)
Relationships
Teacher → Course : One-to-Many
Student ↔ Course : Many-to-Many (via Enrollment)
Activity 3: Online Shopping System
Entities:
Customers, Products, Orders, OrderDetails
Tables
Customers:
Attribute Data Type Constraint CustomerID INT PK, NOT NULL Name VARCHAR(100) NOT
NULL Email VARCHAR(100) UNIQUE Address VARCHAR(200)
Products:
Attribute Data Type Constraint ProductID INT PK, NOT NULL ProductName VARCHAR(100)
NOT NULL
Price DECIMAL(10,2) NOT NULL Stock INT
Orders:
Attribute Data Type Constraint OrderID INT PK, NOT NULL CustomerID INT FK OrderDate
DATE NOT NULL TotalAmount DECIMAL(10,2)
OrderDetails:
Attribute Data Type Constraint OrderDetailID INT PK, NOT NULL OrderID INT FK
ProductID INT FK Quantity INT NOT NULL Foreign Keys
CustomerID → Customers(CustomerID)
OrderID → Orders(OrderID)
ProductID → Products(ProductID)
Relationships
Customer → Orders : One-to-Many
Orders → OrderDetails : One-to-Many
Product → OrderDetails : One-to-Many
Activity 4: Hospital Management
SystemEntities
Doctors, Patients, Appointments
Tables
Doctors
Attribute Data Type Constraint DoctorID INT PK, NOT NULL Name VARCHAR(100) NOT
NULL Specialization VARCHAR(100)
Phone VARCHAR(20)
Patients
Attribute Data Type Constraint PatientID INT PK, NOT NULL Name VARCHAR(100) NOT NULL
Age INT
Phone VARCHAR(20)
Appointments
Attribute Data Type Constraint AppointmentID INT PK, NOT NULL DoctorID INT FK PatientID
INT FK AppointmentDate DATE NOT NULL Foreign Keys
DoctorID → Doctors(DoctorID)
PatientID → Patients(PatientID)
Relationships
Doctor → Appointments : One-to-Many
Patient → Appointments : One-to-Many
Activity 5: School Attendance System
Entities
Students, Teachers, Subjects, Attendance
Tables
Students
Attribute Data Type Constraint StudentID INT PK, NOT NULL Name VARCHAR(100) NOT
NULL Class VARCHAR(50)
Teachers
Attribute Data Type Constraint TeacherID INT PK, NOT NULL Name VARCHAR(100) NOT
NULL
Subjects
Attribute Data Type Constraint SubjectID INT PK, NOT NULL SubjectName VARCHAR(100)
NOT NULL TeacherID INT FK Attendance
Attribute Data Type Constraint AttendanceID INT PK, NOT NULL StudentID INT FK
SubjectID INT FK Date DATE NOT NULL Status VARCHAR(10)
Foreign Keys
TeacherID → Teachers(TeacherID)
StudentID → Students(StudentID)
SubjectID → Subjects(SubjectID)
Relationships
Teacher → Subject : One-to-Many
Student → Attendance : One-to-Many
Subject → Attendance : One-to-Many