CS409 Assignment: 01 Solution

Assignment Details

Due Date:

Monday 3, November, 2025

Extended Date:

Tuesday 4, November, 2025

Assignment: 01

Download Assignment File

Assignment Question Details:

CS409 –Introduction to Database Administration

Assignment No.1

Marks: 20

Instructions

Due Date: November 03, 2025

The purpose of this assignment is to give you hands-on practice The following rules will apply during the evaluation of the assignment.

● Cheating from any source will result in zero marks in the assignment.

● The submitted assignment does NOT open or the file is corrupted will yield zero marks.

● No assignment after the due date will be accepted.

● Submit the assignment in .DOC file format.

● You can use MS Visio/ MS Paint for drawing the ERD Diagram etc.

Objectives and Learning Outcomes:

v Help students understand the three levels of DBMS architecture (Internal, Conceptual, and External).

v Students will be able to identify and classify different operations and descriptions according to the three levels of DBMS architecture.

v Students will develop a clear understanding of how data is viewed and managed at each level in a database system.

v Introduce students to different types of keys used in databases, such as Super Key, Candidate Key, Primary Key, Surrogate Key, and Composite Key.

v Students will be able to differentiate between various key types and their purposes in a relational database.
Students will be able to apply their understanding of keys to design well-structured database tables.

For any query about the assignment, contact at cs409@vu.edu.pk

Question No. 1

The three-level architecture of a Database Management System (DBMS) divides the database structure into three layers: Internal Level, Conceptual Level, and External Level.

Read each of the following scenarios carefully and identify which level of DBMS architecture represents in each case.
Write your answer as Internal Level, Conceptual Level, or External Level.

Scenario	Level of DBMS Architecture
A university’s database administrator defines how student records, courses, and grades are stored in the database, including the data types, relationships, and constraints between tables. Question: Which level of DBMS architecture is represented here?
A student uses the university’s mobile app to view only their own profile, enrolled courses, and GPA. The student cannot see the internal details of how this data is stored or organized in the database. Question: Which level of DBMS architecture is represented here?
A database engineer defines how data will be physically stored on the disk, including file structures, indexes, and access paths to improve performance. Question: Which level of DBMS architecture is represented here?

Question No. 2

Read each of the following scenarios carefully. Analyze the description of the key used in each case and identify the correct type of key from the given options:
(Super Key, Candidate Key, Primary Key, Surrogate Key, Composite Key).
Write your answer in the blank space provided in the table below.

Scenario	Type of Key
1. In the Employee table, the combination of Employee_ID and Email uniquely identifies every employee in the organization.	____________
2. In the Student table, both Roll_Number and CNIC can uniquely identify a student. However, only one of them will be chosen by the database designer to act as the main identifier.	____________
3. In the Customer table, Customer_ID is chosen as the main unique identifier for each customer record.	____________
4. In the Orders table, the system automatically generates a unique Order_ID (e.g., 1001, 1002, 1003) for each new order, even though no natural identifying field exists.	____________
5. In the Library database, the attributes (Book_ISBN, Library_Branch_ID) together uniquely identify each copy of a book available across multiple library branches.	____________