Zybook Week 2

Question 1

What is the entity-relationship model?

Answer 1

high level representation of data requirements, ignoring implementation details.

Three kinds of objects:
entity – person, place, product, concept or activity
relationship – a statement about two entities
attribute – a descriptive property of an entity

Question 2

What is an ER diagram?

Answer 2

A schematic picture of entities, relationships, and attributes.

Entities are rectangles, relationships are drawn as lines
attributes (not always present) appear as additional text within an entity rectangle.

They are read in the direction of the relationship or verb.

Question 3

What is a glossary?

Answer 3

Also known as a data dictionary or repository, this documents additional detail in text format. Includes names, syn, descriptions of entities, relationships and attributes.

Question 4

What is an entity type?

Answer 4

A set of things.

Question 5

What is a relationship type?

Answer 5

A statement about entity types.

Question 6

What is an attribute type.

Answer 6

A set of values.

Question 7

What is an entity instance?

Answer 7

An individual thing. Usually becomes a row.

Question 8

What is a relationship instance?

Answer 8

A statement about entity instances. Usually becomes foreign key values.

Question 9

What is an attribute instance?

Answer 9

An individual value. Usually becoems a column value.

Question 10

What are the three phases of database design?

Answer 10

Analysis - develops an entity-relationship model, capturing data requirements while ignoring implementation details.

Logical design – coverts entity-relationship model into tables, columns and keys for a database system

Physical Design – adds indexes and specifies how tables are organized on storage media

Question 11

What makes up a table?

Answer 11

Table - collection of data organized as columns and rows.

A column is a set of values of the same type. Each column has a different name.

A row is a set ofvalues, one for each column.
A cell is a single column of a single row.

Question 12

What is an empty table?

Answer 12

A table without rows.

Question 13

What is a data type?

Answer 13

Each column has a data type, which defines the format of the values.

Question 14

What are different data types?

Answer 14

Integer, decimal, character, time, binary, spatial, document

Question 15

What structural rules must tables obey?

Answer 15

Normalized - exactly one value exists in each cell
No duplicate column names
No duplicate rows (no two rows may have identical values in all columns)

Question 16

What is data independence?

Answer 16

All relational databases must obey this principle, which states that rows and columns of a table have no inherent order.

Question 17

What is a null value?

Answer 17

A special value that represents missing data. Represents either ‘unknown’ or ‘inapplicable, should not replace 0s.

Question 18

What is an operator?

Answer 18

A symbol that computes a value from one or more other values, called operands.

Question 19

What is an aggregate function?

Answer 19

operates on numeric values from multiple rows, including only rows selected by the WHERE clause.

Includes: SUM, AVG, MAX, MIN

Question 20

What is a primary key?

Answer 20

A column, or group of columns, used to identify a row.

Question 21

What two rules must primary keys obey?

Answer 21

Values must be unique within the column.

Values may not be null

Question 22

What is a composite primary key?

Answer 22

Consists of multiple columns. A simple one consists of a signle column.

Question 23

What makes up a minimal primary key?

Answer 23

All columns are necessary for uniqueness.

Question 24

What three rules most composite primary keys obey?

Answer 24

Column values, when grouped must be unique
May not contain null
must be minimal

Question 25

What is a foreign key?

Answer 25

A column or a group of columns that refer to a primary key. Non-null foreign keys must match some value of the primary key.