My Summary

Question 1

Database management system (DBMS):

Answer 1

collection of programs Manage structure and control access to data

Question 2

Advantages of the DBMS:

Answer 2

• Improved data sharing
• Improved data security
• Better data integration
• Minimised data inconsistency
• Improved data access
• Improved decision making
• Increased end-user productivity

Question 3

What does poorly designed databases cause?

Answer 3

Causes difficult -to- trace errors

Question 4

Reasons for studying file systems:

Answer 4

– Complexity of database design easier to understand

– Understanding file system problems helps to avoid problems with DBMS systems

– Knowledge of file system useful for converting file system to database system

Question 5

Summary of file system limitations(Problems with file system limitations) :

Answer 5

– Requires extensive programming

– Cannot perform ad hoc queries

– System administration complex and difficult

– Difficult to make changes to existing structures

– Security features likely to be inadequate

Question 6

Structural independence:

Answer 6

Change file structure without affecting data access

– Programs need not be modified when file structure changes

Question 7

Data independence:

Answer 7

data storage characteristics do not affect data access

Question 8

Logical data format:

Answer 8

How a human views the data

Question 9

Physical data format:

Answer 9

How computer must work with data

Question 10

What does DEPENDENT mean

Answer 10

Dependent means that each program must contain reference to physical format

Question 11

What does INDEPENDENT mean

Answer 11

Independent means that each program must contain reference to only to logical format

Question 12

Data redundancy

Answer 12

Same data stored unnecessarily in different places

Question 13

Data inconsistency:

Answer 13

Different and conflicting versions of same data occur at different places

Question 14

Data anomalies:

Answer 14

abnormalities when all changes in redundant data not made correctly
– Update anomalies
– Insertion anomalies
– Deletion anomalies

Question 15

Database system: defines and regulates the

Answer 15

1. Collection
2. Storage
3. Management
4. Use of data

Question 16

Five major parts of a database system:

Answer 16

1. Hardware
2. Software
3. People
4. Procedures
5. Data

Question 17

DBMS Functions

Answer 17

Most functions transparent to end users
• Data dictionary management
• Data storage management
• Data transformation and presentation
• Security management

Question 18

Disadvantages of database systems

Answer 18

• Increased costs
• Management complexity
• Maintaining currency
• Vendor dependence
• Frequent upgrade/replacement cycles

Question 19

What is Data models

Answer 19

– Relatively simple representations of complex real-world data structures
Often graphical

Question 20

What are the following things?

1. Entity
2. Attribute
3. Relationship

Answer 20

• Entity: anything about which data are to be collected and stored
• Attribute: a characteristic of an entity
Relationship: describes an association among entities:

Question 21

What type of relationships are there?

Answer 21

– One-to-many(1:M)relationship
– Many-to-many (M:N or M:M) relationship
– One-to-one (1:1) relationship

Question 22

What is a Constraint

Answer 22

a restriction placed on the data

Question 23

Sources of business rules:

Answer 23

– Company managers
– Policy makers
– Department managers
– Written documentation

Question 24

Composite key

Answer 24

Composed of more than one attribute

Question 25

Key attribute

Answer 25

Any attribute that is part of a key

Question 26

Superkey

Answer 26

Any key that uniquely identifies each row

Question 27

Candidate key

Answer 27

A superkey without unnecessary attributes

Question 28

What is a Null and explain

Answer 28

- No data entry - Not permitted in primary key - Should be avoided in other attributes - Can create problems when functions such as MATH

Question 29

What do designers use flags?

Answer 29

To avoid nulls

Question 30

What is a Foreign key(FK)

Answer 30

-An attribute whose values match primary key values in the related table

Question 31

Referential integrity

Answer 31

-FK contains a value that refers to an existing valid tuple(row) in another relation

Question 32

Secondary key

Answer 32

-Key used strictly for data retrieval purposes

Question 33

What is Natural Join

Answer 33

_Links tables by selecting rows with common values in common attributes(s)

Question 34

What is outer join

Answer 34

-Matched pairs are retained, and any unmatched values in other table are left null

Question 35

In which two cases are Composite primary keys useful?

Answer 35

1. As identifiers of composite entities in which each primary key combination is allowed once in M:N relationship 2. As identifiers of weak entities in which weak entity has a strong identifying relationship with the parent entity

Question 36

When to use Surrogate Primary Keys

Answer 36

1. When there is n natural keys 2. If Selected candidate key has embedded semantic contents If Surrogate key is used use unique index and nit null constraints.

Question 37

What is Normalisation?

Answer 37

Process for evaluating and correcting table structures to minimise data redundancies • Reduces data anomalies

Question 38

Series of stages called normal forms:

Answer 38

First normal form (1NF) Second normal form (2NF) Third normal form (3NF) – 2NF is better than 1NF; 3NF is better than 2NF – For most business database design purposes, 3NF is as high as needed in normalisation

Question 39

Define Functional Dependence

Answer 39

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B

Question 40

Steps for conversion to first normal form (1NF)

Answer 40

Step 1 : Eliminate the repeating groups -eliminate nulls: each repeating group attribute contains an appropriate data value Step 2: Identify the Primary Key - Must uniquely identify attribute value - New key must be composed Step 3: Identify All Dependencies -Dependencies are depicted with a diagram

Question 41

Steps for conversion to Second normal form (2NF)

Answer 41

Step 1: Make New Tables to eliminate partial dependencies - Write each key component on separate line, then write original (composite) key on last line - Each component will become e key in the new table Step 2: Assign Corresponding Dependent attributes - Determine attributes that are dependent on the other attributes - At this point, most anomalies have been eliminated