CSC2460 Database Systems Problem Solving Assessment T1 | USQ

Assessed Course Learning Outcomes

• CLO1: Apply conceptual modelling techniques and data integrity-ensuring normalisation to implement a demonstrably high-quality relational schema into an RDBMS using SQL.

Rationale

This assessment aims to evaluate students' understanding and emerging skills in:

• Modelling relational schemas and implementing using database languages,
• Understanding relational algebra concepts and operations
• Working with database keys and constraints
• Reflecting on ethical considerations in database design

Task Instructions

Students will complete four (4) tasks demonstrating their knowledge of:

• Relational schemas, instances, and database concepts (14 marks)
• Ethics and professional conduct in database management (2 marks)
• Relational keys and integrity constraints (10 marks)

Objective: The aim of Problem Solving 1 is to assess your understanding and emerging skills in modelling relational schemas and implementing those using database languages. It also assesses your initial reflection on the ethical issues involved in database design. As such, this assignment aligns with Course Learning Outcomes 1 and 4.

Keywords: Relational schemas, instances, keys; Relational Algebra; ethics

Task 1 (14 Marks)

Topic: Relational schemas and instances

Submission: A PDF document that is to be uploaded to StudyDesk. The PDF document can be created using any word processing application. Ensure your document has your name and student number, a clear title “CSC2460-T1-2025 Problem Solving 1”.

1a. Why is Relational Algebra treated as a procedural language? (0.5 mark )

1b. A cell in a relation is the intersection of a row and a column of the relation. Suppose that we want to generate a relation that has a total o eight(8) cells, then how many different relations can you create that satisfy this requirement?  Please also give the cardinality and degree of each possible relation. (1 mark)

1c. What’s the key difference between the abbreviated syntax and the full syntax of a relational schema? (0.5 mark )

1d. Suppose we want to create a relation to store course information. The relation is called Course Info and it has three attributes, i.e., course code, number of enrolled students and name of the examiner. Course code always follows the format of CSCXXXX, where X is a digit from 0 to 9, the number of enrolled students is a positive integer, and the name of the examiner is a string. Write the schema for the relation using both the full and the abbreviated syntax (as described in Appendix 1 at the end of this document). (2 marks)

1e. What is the advantage of the three-tier client/server database architecture against the two-tier alternative? (2 mark )

1f. Will selection and projection operations cause information loss horizontally or vertically? Please give a simple example for both operations for explanations(i.e., you need to give one example for the selection operation and another one for the projection operation). (2 mark )

1g. We know that the sequence of relational algebra operations is order-sensitive. Please give an example to show that if you change the order of the sequence of relational algebra operations that were executed correctly originally, it may produce an error, and explain why. (3 mark )

1h. For two relations A and B, with cardinality(A)=3 and cardinality(B)=4, please calculate the maximum and minimum values of the cardinality of  A∪B, A∩B and B−A, respectively. (3 mark)

Task 2 (2 Marks)

Topic:

Reflection on Ethics and Professional Conduct. To assist students in answering the questions in Task 2, some supplementary reading materials on data ethics have been provided on Studydesk during Week 1.

Submission: In the same PDF document that you created for Task 1, you will create a new top-level section on a new page, responding to the questions below.

Write two (2) paragraphs that together cover between a half and a full page in your PDF.

• Paragraph 1 should provide your initial reflection on what kinds of data could be stored in a relational database that could be considered private or sensitive. (1 mark)
• Paragraph 2 should provide your initial reflection on the operations (give at least two operations) that Database Administrators (DBAS) can apply to protect and safeguard such private information. (1 mark)

Task 3 (10 Marks )

Topic: Relational Keys
Submission: In the same PDF document that you created for Tasks 1 and 2, you will create a new top-level section on a new page, responding to the questions below.

Suppose that we have the following relational schema:

Car Info (registration, VIN, owner, year)
Driver Info (name, employer, phone)

Suppose that {registration} and {VIN} in the Car Info relation are individually unique in the relation.

The owner attribute of the CarInfo relation corresponds to the name attribute of the Driver Info relation. For each owner attribute value of the CarInfo relation, there is one and only one corresponding name attribute in the Driver Info relation. Only the name attribute values are unique in the Driver Info relation.

Please answer the following questions about relational keys:

3a. How many super keys are there in the CarInfo relation? List them. (2 marks)
3b. How many candidate keys are there in the CarInfo relation? List them. (1 mark)
3c. Identify the candidate key for the Driver Info relation. (1 mark)
3d. Identify the foreign key(s) between the CarInfo and Driver Info relations. Please use the following notation for each foreign key: (1 mark)
BaseTableName (Attributes) References ForeignTableName (Candidate key)
3e. Consider the following instances for the CarInfo and Driver Info relations where the foreign key constraint has been reinforced. Car Info relation:

DriverInfo Relation:

Indicate where the relational integrity rules are broken in the two relations and explain. Please also give the type of integrity rules that are violated. (5 marks)

Task 4 (4 Marks)

Topic: Relational Algebra
Submission: In the same PDF document that you created for Tasks 1, 2 and 3, you will create a new top-level section on a new page, responding to
The questions below.

The following relational schema models a simple database for an airline performance tracking system, with two tables: Flight and Pilot. Each flight is operated by a captain and assisted by a copilot. After each flight, both pilots receive performance scores. It is assumed that not all pilots in the Pilot table operate flights.

Flight (captain, copilot, flightDate, scoreCaptain, scoreCopilot) 
Pilot(name, airline, experience)

The following two Foreign Keys exist in the schema between the two tables:

FK Flight (captain) references Pilot (name)
FK Flight (copilot) references Pilot (name)

Please answer the following three questions in your PDF.

4a. What is the meaning of the following SQL expression? What information will be retrieved? (1 mark)

SELECT scoreCaptain + scoreCopilot
FROM Flight, Pilot
WHERE (captain = name OR copilot = name) AND airline = 'SkyHigh'

4b. Present the corresponding relational algebra expression for the SQL statement in the previous task (4a). (2 marks)

4c. Present the relational algebra expression for the following query: Find the flight dates and the absolute difference in the performance scores between the captain and the copilot when a pilot from the "SkyHigh" airline, who has more than 10 years of experience, participated in the flight. You may use the ABS () function to obtain the absolute difference. (1 mark)