Section A in Sick Semester Test 1 2016

Question 1

Briefly describe the following terms as used when referring to Database Systems.

a) Data

Answer 1

a) Data  Raw facts from which the required information is derived. Data have little meaning unless they are grouped in a logical manner.

Question 2

Briefly describe the following terms as used when referring to Database Systems.

b) Field

Answer 2

b) Field  A character or a group of characters (numeric or alphanumeric) that describes a specific characteristic. A field may define a telephone number, a date, or other specific characteristics that the end user wants to keep track of.

Question 3

Briefly describe the following terms as used when referring to Database Systems.

c) Record

Answer 3

c) Record  A logically connected set of one or more fields that describes a person, place, event, or thing. For example, a CUSTOMER record may be composed of the fields

CUST_NUMBER,
CUST_LNAME,

Question 4

Briefly describe the following terms as used when referring to Database Systems.

d) File

Answer 4

d) File  A collection of related records that contain information of interest to the end user. Related records reflect a relationship based on function.

Question 5

2. Although using a database system has advantages over previous data management approaches, database systems carry significant disadvantages. Briefly list five disadvantages of making use of a database system.

Answer 5

 Increased cost
 Management complexity
 Maintenance currency (system should be kept current)
 Vendor dependency
 Frequent upgrade /replacement cycles

Question 6

3. Name the three components that an implementation ready data model should contain.

Answer 6

 A description of the data structure that will store the end-user data.

 A set of enforceable rules to guarantee the integrity of the data.

 A data manipulation methodology to support the real-world data transformations.

Question 7

Name and briefly describe the three parts involved in any SQL-based relational database system.

Answer 7

 The end-user interface. – The interface allows the end user to interact with the data (by auto- generating SQL code).

 A collection of tables stored in the database. In a relational database, all data are perceived to be stored in tables. The tables simply “present” the data to the end user in a way that is easy to understand.

 SQL engine. – The SQL engine executes all queries or data requests.

Question 8

5. Briefly describe the use of the UNION operator

Answer 8

UNION combines all rows from two tables, excluding duplicate rows. The tables must have the same attribute characteristics (the columns and domains must be compatible) to be used in the UNION. When two or more tables share the same number of columns, when their corresponding columns share the same (or compatible) domains, they are said to be union-compatible.

Question 9

6. There are circumstances when an entity requires a composite primary key. Clearly indicate the circumstances in which a composite primary key would be an appropriate solution used to uniquely identify a record associated with an entity set.

Answer 9

Composite primary keys are particularly useful in two cases:
 As identifiers of composite entities, where each primary key combination is allowed only once
in the M:N relationship.
 As identifiers of weak entities, where the weak entity has a strong identifying relationship with
the parent entity.

Question 10

7. A characteristic of a good primary key is that it should not “change over time”. Indicate the implication of this characteristic by referring to the possible semantic meaning of attributes. Make use of a simple example to motivate your answer.

Answer 10

If an attribute has (1) semantic meaning, it might be subject to updates. This is why names do not make good primary keys. (2) For example, if you have JAN PAP as the primary key, what happens when he changes his name? (3) If a primary key is subject to change, the foreign key values must be updated, thus adding to the database work load. Furthermore, (4) changing a primary key value means that you are basically changing the identity of an entity. In short, the PK should be permanent and unchangeable.

Question 11

Define the concept of a functional dependency. Make use of a short example to support your answer.

Answer 11

 Attribute A determines attribute B if all the rows in the table that contain one particular value for A also have one value for attribute B.

 Eg. Key Attribute = determinant, Non-key attribute = dependent.

Question 12

9. Define the concept of a partial dependency. Make use of a short example to support your answer

Answer 12

 Partial dependency: Attribute B is dependent on only some of the attributes making up the composite primary key.

Eg. Composite key = determinant, Non-key attribute = dependent.

Question 13

Briefly explain how database designers design and normalize databases.

Answer 13

First, an ERD is created through an iterative process. You begin by identifying relevant entities, their attributes, and their relationships. Then you use the results to identify additional entities and attributes. The ERD provides the big picture, or macro view, of an organization’s data requirements and operations.

 Second, normalization focuses on the characteristics of specific entities; that is, normalization represents a micro view of the entities within the ERD. The normalization process might yield additional entities and attributes to be incorporated into the ERD. Therefore, it is difficult to separate normalization from ER modelling; the two techniques are used in an iterative and incremental process.

Question 14

1:1 Relationships should be rare or plenty in any relational database design

Answer 14

Rare

Question 15

Data redundancy leads

Answer 15

to data anomalies – Can destroy the effectiveness of the database

Question 16

Unary relationship:

Answer 16

Association is maintained within a single entity

Question 17

Binary relationship:

Answer 17

Two entities are associated

Question 18

Ternary relationship:

Answer 18

Three entities are associated

Question 19

Optional participation:

Answer 19

One entity occurrence does not require corresponding entity occurrence in particular relationship

Question 20

Mandatory participation

Answer 20

One entity occurrence requires corresponding entity occurrence in particular relationship