02 - Relational Databases

Question 1

What are:

Attributes?

Answer 1

Columns. Allowed data (e.g. INT, STRING, etc) in an attribute is called attribute domain.

Question 2

What are:

Tuples?

Answer 2

records. A record in a table is a row, with the same number of attributes (columns). Important: Each tuple within a table needs to be unique as required by the relational model.

Question 3

What are: Schema?

Answer 3

(meta-data) - Specification of how data is to be structured logically, defined at setup, rarely change. E.g. Student (SID int, name string, age int, gpa real)

Question 4

What are: Instance?

Answer 4

Content of a table, changes rapidly, but always conforms to the schema.

Question 5

What is a key?

Answer 5

a set of one or more attributes (columns) in the table that have certain properties.

Question 6

Explain: Compound key?

Answer 6

Including two or more attributes

Question 7

Explain: Superkey?

Answer 7

No two tuples have the same values. Key attributes that can uniquely identify a row. Several super keys can present in a table.

Question 8

Explain: Candidate key?

Answer 8

It’s a minimum super key. A super key reduced to the least amount of attributes needed to uniquely identify a row.

Question 9

Explain: Primary key?

Answer 9

Used to uniquely identify of find the tuples in a table, every tuple in a relational database has its own primary key. Normally it will be chosen from one of the primary keys.

Question 10

Explain: Secondary Key?

Answer 10

Used to look up tuples, not uniqueness

Question 11

Explain: Foreign Key?

Answer 11

used as constraint

Question 12

What is an index?

Answer 12

database structure, quicker and easier to find tuples based on values in one or more attributes. Not the same as key.

Question 13

What are: Constraints?

Answer 13

Related to a particular attribute in a table. Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table.

Attribute required. Special value null represents an empty value.

Question 14

Explain: Primary Key Constraints?

Answer 14

Entity integrity. No two records can have identical values for the primary key attribute of a table. All of the attributes that make up the primary key have non-null values.

Question 15

Explain: Foreign Key Constraints?

Answer 15

Foreign key: refer to a key in another table. Referential integrity constraint. A tuples’ value in one or more attributes in one table must match the values in another table.

Question 16

DB Operations: Selection?

Answer 16

Select some or all of the tuples in a table. Ex. Select only the students tuples where their gpa is greater than 2.5.

Question 17

DB Operations: Projection?

Answer 17

Drops attributes from a table. Ex. list only students name and not their ages.

Question 18

DB Operations: Union?

Answer 18

Combines tables with similar columns and removes duplicates.

Question 19

DB Operations: Intersection?

Answer 19

Finds the records that are the same in two tables.

Question 20

DB Operations: Difference?

Answer 20

Selects the tuples in one table that are not in a second table

Question 21

DB Operations: Cartesian Product?

Answer 21

Creates a new table containing every tuples in a first table combined with every tuples in a second table.

Question 22

DB Operations: Join?

Answer 22

Tuples in one table are paired only with those in the second table if they meet some conditions. Similar to cartesian product.

Ex: Student <-> Enroll = {(Rian, COMP40110), (Rian,COMP40120), (Alfie,COMP40120), (Alfie,COMP41430),…}

Question 23

DB Operations: Divide?

Answer 23

Opposite of the Cartesian product. Uses one table to partition the tuples in another table.

Question 24

How can you make DB retrevial faster?

Answer 24

keys & indexing

Question 25

What are the three main vital things in DB design?

Answer 25

• Inclusion of all information
• Selection of correct data types
• Creation of keys / indexes

Question 26

What is Database Normalisation?

Answer 26

Normalisation is the process of efficiently organising data in a database. There are two goals of the normalisation process:

• Eliminating redundant data (storing the same data in multiple tables)
• Ensuring data dependencies make sense (only storing related data in a table)

Question 27

What are the Normal Forms (NFs)?

Answer 27

Strictly speaking there are seven normal forms (0NF through 5NF and BCNF). We will only look at 1NF to 3NF. As a rule databases are designed using 3NF. Very occasionally a database will be designed to 4NF.

Question 28

Explain: First Normal Form (1NF)?

Answer 28

1NF gives the most basic rules for organising information in a DB.

1. Eliminate duplicate fields from the same table
2. Create separate tables for each group of related data
3. Identify each row with a unique value (primary key)

Question 29

Explain: Second Normal Form (2NF)?

Answer 29

Second Normal form removes more duplicate information:

• There must not be any partial dependency of any column on primary key

The guidelines are:

• Meet 1NF requirements
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors (foreign keys)

Question 30

Explain: Third Normal Form (3NF)?

Answer 30

3NF has the following requirements:

• Meet all 2NF requirements
• Remove fields that are not dependent upon the primary key
• In third normal form every field should be dependent on the key.

Question 31

What is the general naming of common database operations?

Answer 31

• Selection
• Projection
• Union
• Intersection
• Difference
• Cartesian Product
• Join
• Divide