CIS2002 S3 2020 Exam Review

Exam [60%, 100 marks]

Three hours duration. Open online exam.

Part A [20 marks]

Part A has 20 multiple choice questions worth one mark each. You will record your answers on the Multiple Choice Answer Pages provided. If you are not sure which is the correct answer, attempt to eliminate answers that are clearly incorrect.

Some questions involve SQL queries. The queries are drawn largely from the material covered in the required Casteel readings. Only the most important functions are examinable in the SQL section, but ANY function could be included in this section.

Part B [80 marks]

Part B has four questions, each worth varying marks:

21. SQL Queries (15%) (5 Questions)
22. Data Modelling (ERD, list of relations, CREATE TABLE) (35%)
23. Normalisation (15%)
24. Database Theory (15%)

Question 21: SQL Queries [15 marks]

This section has five (5) questions each worth 3 marks. The questions cover intermediate to advanced SQL queries based on chapters 3 and 8 –12 of the textbook.

Focus on topics such as WHERE conditions including LIKE, set operators, table joins, sub-queries, and functions with the use of GROUP BY and HAVING clauses.

The following functions are examinable:

• Single Row Functions: (SUBSTR, ROUND, TRUNC, MONTHS_BETWEEN, ADD_MONTHS, TO_DATE, TO_CHAR, SYSDATE, NVL)
• Group Functions: (SUM, AVG, COUNT, MAX and MIN)

Question 22: Data Modelling [35 marks]

This section has a case study of limited scope. Similar to Assignment 2, you are required to prepare:

• an ER diagram
• A list of relations (equivalent to Finkelstein entity list)
• An Oracle SQL table create statement

Read the description carefully. Look for nouns (person, place or thing) that will be entities. Sometimes verbs can also be an entity. Look carefully for entities that can be broken down into categories – TYPE with principal and secondary entities.

• Resolve all M:M relationships via an intersection entity
• Underline and hash primary keys in list of relations
• Hash but no underline in foreign keys
• Many foreign keys read directly from ERD – mandatory one on the opposite side indicates a foreign key
• Review the structure of TYPE/SECONDARY entities – make sure you place a mandatory one on the principal entity and optional one on each of the secondaries.
• Double check the ERD and list of relations so that all entities and relationships in your ERD are properly reflected in the list of relations along with the attributes.
• You will also be asked to write a SQL command to create a table for a relation along with primary key and foreign key constraints (you must assume logical data types for the table columns).

Question 23: Normalisation [15 marks]

Again, similar to Assignment 2, you are asked to produce a set of relations (equivalent to Finkelstein entity list) in third normal form (3NF) from an un-normalised relation. Here’s a list of steps for normalisation:

1. Identify the primary key (hash and underline)
2. Identify derived attributes (place ‘curly’ brackets around them)
3. Remove repeating groups. Often the biggest problem. Anything with curved brackets is a repeating group which means a M:M relationship with the enclosing relation – split such relations!
4. Resolve 2NF where non-key attribute must be functionally dependent on the WHOLE key
5. Resolve transitive dependencies, i.e. where one non-key attribute depends upon another
6. Make sure you address all the additional notes provided along with the un-normalised (0NF) relation.

Question 24: Database Theory [15 marks]

The EXAM REVIEW block on the Study Desk has Word file titled sample Theory topic questions.

• One or two questions on the exam may be drawn from these topics but they may not be exactly the same question.
• Exam questions will however be related to one of the theory topics listed in the sample questions.
• Prepare by gaining an overall concept of the theory topics by reading selected readings. Try to understand key definitions, concepts, examples, advantages/ disadvantages, architecture diagrams, etc.
• You may be required to provide an analytical answer rather than purely descriptive.

The post Data Modelling appeared first on My Assignment Online.