Solved — Computer Dashain Database
Assignment (A-Level 9618)
Style: Concise, exam-marking style answers. Source: User-uploaded PDF 'Computer Dashain
Database Assignment (1).pdf'.
Part 1 — Questions 1–10
Q1 (e): SELECT LessonDateTime FROM LESSON WHERE InstructorID='Ins01' AND
LessonDateTime>CURRENT_TIMESTAMP;
Q3 (b): Tasks using DBMS developer interface: (1) Design/modify schema (2) Create/test
queries (3) Build reports/forms.
Q4 (b)(i): File-based issues: redundancy, poor integrity, weak concurrency/security.
Q4 (b)(ii): Schema levels: Logical (structure); Physical (storage). FK example: ALTER TABLE
LICENCE ADD CONSTRAINT fk_lic_customer FOREIGN KEY(CustomerID) REFERENCES
CUSTOMER(CustomerID);
Q5: Data dictionary stores metadata. Query processor parses and optimizes SQL. SQL
example: SELECT [Link],[Link] FROM EMPLOYEE e JOIN DEPARTMENT d ON
[Link]=[Link] WHERE [Link]='F' AND [Link]='Finance';
Q6: Data redundancy = duplicate data. Normalization + keys reduce redundancy. Security vs
Integrity: access vs correctness. Security features: auth, GRANTs.
Q7: CUSTOMER(CustomerID PK,...); ACCOUNT_TYPE(AccountTypeID PK,...);
CUSTOMER_ACCOUNT(AccountID PK,CustomerID FK,...).
Q8: Relational DB features: constraints, normalization, transactions, SQL. 1NF–3NF
definitions. CREATE DATABASE SPORTS_CLUB; CREATE TABLE SESSION(...). SELECT
FirstName,LastName FROM MEMBER WHERE MembershipType='Peak';
Q9: CUSTOMER(CustomerID PK,...), ROOM(RoomNumber PK,...), BOOKING(BookingID
PK,CustomerID FK,...). Developer interface builds tables/forms; query processor executes
SQL queries.
Q10: [Link] FK references [Link]. Referential integrity ensures valid links.
CREATE TABLE USER(UserID INT PK,...); ALTER TABLE USER ADD Country
VARCHAR(100);
Part 2 — Questions 11–21
Q11: Data integrity = accuracy/consistency. Use constraints. CREATE TABLE
PLAYER(PlayerID INT PK,UserName VARCHAR(50) UNIQUE,Email VARCHAR(255),Score
INT DEFAULT 0); ALTER TABLE PLAYER ADD DateOfBirth DATE;
Q12: PK on MOVIE, FK in SCREENING. ALTER TABLE MOVIE ADD ProductionCompany
VARCHAR(255); SELECT Title,Rating FROM MOVIE m JOIN SCREENING s ON
[Link]=[Link] WHERE [Link]=3;
Q13: Many-to-many ward–nurse via B_WARD_NURSE(NurseID,WardName)
PK(NurseID,WardName). SELECT NurseID,FamilyName FROM B_NURSE WHERE
Specialism='THEATRE'; UPDATE B_NURSE SET FamilyName='Chi' WHERE NurseID='076';
Q14: SHOP_SUPPLIER(ShopID,SupplierID). Secondary key improves lookup. SELECT
ShopID,Location FROM SHOP WHERE RetailSpecialism='GROCERY'; INSERT INTO
SHOP_SUPPLIER VALUES(8765,'SUP89');
Q15: Add Status field ('Attended','NoShow'). UPDATE DOCTOR SET DoctorID='017' WHERE
DoctorID='117'; Care: FK cascade. SELECT AppointmentDate,AppointmentTime FROM
APPOINTMENT WHERE PatientID=556;
Q16: PKs: CLIENT(ClientName PK), VISIT(ClientName,VisitDate PK),
INTERVIEW(ClientName,VisitDate,StaffID PK). ALTER TABLE VISIT ADD VisitReportText
TEXT; UPDATE CLIENT SET ClientName='Albright Holdings' WHERE ClientName='ABC
Holdings'; SELECT StaffID FROM INTERVIEW WHERE ClientName='New Age Toys' AND
VisitDate='2016-10-13';
Q17: DBMS = Database Management System. Security: roles, least privilege, encryption.
Secretary uses query processor for reports. CLASS_GROUP: StudentID FK, ClassID FK,
PK(StudentID,ClassID).
Q18: SELECT [Link],[Link],[Link] FROM STUDENT s JOIN
STUDENT_QUALIFICATION q ON [Link]=[Link] WHERE [Link]='A';
Q19: Referential integrity = FK must match existing PK; enforced with FK constraints and
CASCADE.
Q20: Advantages: integrity, reduced redundancy, query power, concurrency, security.
CREATE TABLE CLASS(ClassID INT PK,ClassName VARCHAR(100),InstructorID
INT,MaxCapacity INT,Room VARCHAR(20));
Q21: Relationship via FK; not 3NF if transitive deps/repeats. PRODUCT(ProductID PK,...),
SALES(SaleID PK,...), SALES_PRODUCT(SaleID FK,ProductID FK,Quantity,Price
PK(SaleID,ProductID)).