FAST-National University of Computer and Emerging Sciences (NUCES)
Chiniot-Faisalabad Campus
Assignment 2
Course/Topic
Database
Assignment Guidelines:
1. Deadlines should be kept in mind. No extension in assignment dates would be given. No
late submission will be accepted.
2. This is an individual and Handwritten assignment. PLAGIARISM IS NOT
ACCEPTABLE AT ALL! Zero marks will be given in case of plagiarism.
3. Deadline = Feb 19 , 2025 Deadline is hard and firm.
Question 1:
Scenario 1: Online Learning Platform with Complex Relationships
An online learning platform offers courses, modules within those courses, and various
learning resources (videos, articles, quizzes) associated with modules. Users can enroll in
courses. Instructors create courses and modules and upload resources. A resource can be part
of multiple modules (e.g., a general introduction video used in several modules). Users can
track their progress on individual resources and modules.
Tables: Users, Courses, Modules, Resources, Enrollments, UserProgress, Instructors,
ModuleResources
Challenge Questions:
● Design the table schemas, including appropriate primary and foreign keys.
Justify your choices for primary keys, especially where there might be ambiguity
(e.g., ModuleResources). Consider composite keys.
● How would you handle the many-to-many relationship between resources and
modules? Explain why your approach is better than alternatives.
● How would you design the UserProgress table to efficiently track user progress
on both modules and individual resources? What are the trade-offs of different
approaches?
● An instructor can create multiple courses and modules. How would you
represent this relationship, and what are the implications for data integrity?
● Could a resource have multiple instructors? How would you design the database to
accommodate this?
● Imagine a scenario where you want to track which version of a resource a user
interacted with (e.g., if a video is updated). How would you modify the database
design?
FAST-National University of Computer and Emerging Sciences (NUCES)
Chiniot-Faisalabad Campus
Scenario 2: Scientific Research Database with Data Versioning
A scientific research database stores data from experiments. Experiments are conducted by
researchers and generate datasets. Datasets can be analyzed, and analyses can produce results.
Datasets can be revised or updated, creating new versions. Researchers can collaborate on
experiments.
Tables: Researchers, Experiments, Datasets, Analyses, Results, ExperimentResearchers
Challenge Questions:
● Design the table schemas, including primary and foreign keys. Pay particular
attention to how you will manage dataset versions. Why did you choose your specific
versioning strategy (e.g., new table, version number in existing table)?
● How would you represent the many-to-many relationship between researchers
and experiments?
● How would you link analyses to specific dataset versions?
● How would you design the Results table to store results that might be associated
with different analyses of the same dataset?
● Consider the scenario where you want to track the provenance of a result –
which dataset version, which analysis, and which researcher contributed to it.
How would you implement this?
● How can you ensure data consistency when updating a dataset? What are the
challenges, and how can you mitigate them?
Scenario 3: Event Ticketing System with Complex Pricing
An event ticketing system sells tickets for events. Events have different ticket types (e.g.,
VIP, General Admission). Ticket prices can vary based on the event, the ticket type, and the
purchase date (early bird discounts, last-minute price increases). Customers purchase tickets.
Tables: Events, TicketTypes, Tickets, Customers, Pricing
Challenge Questions:
● Design the table schemas. The Pricing table is the most critical. How will you
represent the complex pricing logic? Justify your design choices. Consider the trade-
offs between simplicity and flexibility.
● How would you handle special promotions or discounts that apply to specific
customers or groups of customers?
● How would you prevent overbooking (selling more tickets than available)?
● How would you record the purchase of a ticket, linking it to the customer, event,
ticket type, and price at the time of purchase?
● How would you handle refunds or ticket transfers?
FAST-National University of Computer and Emerging Sciences (NUCES)
Chiniot-Faisalabad Campus
● Imagine the system needs to support different payment methods. How would you
extend the design?
Question 2:
Identify only primary keys, foreign keys, candidate keys, and composite primary keys in the following
tables. Also show referential integrity constraint between each table. (15)
Question 3: Consider the following `Students` table:
| StudentID | Name | Email | CourseID | ProfessorID |
|-----------|------------|---------------------|----------|-------------|
| 1001 | Ali Khan | ali.khan@mail.com | 101 | P01 |
| 1002 | Sara Ali | sara.ali@mail.com | 102 | P02 |
FAST-National University of Computer and Emerging Sciences (NUCES)
Chiniot-Faisalabad Campus
| 1003 | Ahmed Rizvi| ahmed.rizvi@mail.com | 103 | P01 |
| 1004 | Fatima Noor| fatima.noor@mail.com | 101 | P03 |
| 1005 | Taha Ali | taha.ali@mail.com | 102 | P02 |
1. Which column or combination of columns would uniquely identify each row in this table?
2. Which column is most likely to represent a relationship between `Students` and another
table, like a `Courses` table?
3. If we wanted to enforce a constraint that no two students could have the same email
address, which column would need to be the key?
4. If the table had no constraints and we could have multiple students with the same email
address, what would happen if we were to insert a student with the email
`ali.khan@mail.com` again?
5. Which combination of columns might be used to represent a composite key in the
`Students` table?
6. Which column could be considered a foreign key in this table, referring to another table
(`Professors`)?
Question 4: Answer to these short questions
FAST-National University of Computer and Emerging Sciences (NUCES)
Chiniot-Faisalabad Campus
1. If a table contains StudentID, CourseID, and Grade, and each student can enroll in
multiple courses, but each course can have multiple students, what type of key should be used
to uniquely identify each record in the table?
2. A table has the following columns: EmployeeID, DepartmentID, ManagerID.
The EmployeeID is unique, and each department can have multiple employees, but each
employee has one ManagerID (which is also an EmployeeID). What type of key
is ManagerID in this table?
3. In a database table with a primary key on column OrderID and a foreign key in the
column CustomerID referring to the Customers table, what would happen if a customer was
deleted from the Customers table?
4. In a table with the columns OrderID, ProductID, and Quantity, if an order can have
multiple products, and each product can appear in many orders, what type of key would the
combination of OrderID and ProductID form?
5. You have a table with a candidate key consisting of {EmployeeID, DepartmentID}. If
you create a composite keyusing {EmployeeID, DepartmentID, ProjectID}, what will
happen to the uniqueness of the records?
6. In a table, you have ProductID (Primary Key), SupplierID (Foreign Key to
the Suppliers table), and Price. What would happen if the SupplierID is set to NULL for
a product that is currently supplied by a supplier?
7. You have two tables: Students with StudentID, Name, and CourseID,
and Courses with CourseID, CourseName. What is the best way to enforce that every student
must be enrolled in a valid course?
8. If a table has EmployeeID (Primary Key), EmployeeEmail (Unique Key),
and EmployeePhone (Unique Key), can multiple employees share the same phone number?
Justify your answer.
FAST-National University of Computer and Emerging Sciences (NUCES)
Chiniot-Faisalabad Campus
9. In a normalized database, if a table has EmployeeID, EmployeeName, and ManagerID,
where ManagerID refers to an EmployeeID of another employee, what type of dependency
exists between EmployeeID and ManagerID?
10. A table has columns StudentID, CourseID, and InstructorID, and you need to create a
composite key. If a student enrolls in multiple courses, and each course is taught by one
instructor, what would be the best way to structure the keys?
Question 5:
FAST-National University of Computer and Emerging Sciences (NUCES)
Chiniot-Faisalabad Campus
