Zybook Week 2 Flashcards
What is the entity-relationship model?
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
What is an ER diagram?
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.
What is a glossary?
Also known as a data dictionary or repository, this documents additional detail in text format. Includes names, syn, descriptions of entities, relationships and attributes.
What is an entity type?
A set of things.
What is a relationship type?
A statement about entity types.
What is an attribute type.
A set of values.
What is an entity instance?
An individual thing. Usually becomes a row.
What is a relationship instance?
A statement about entity instances. Usually becomes foreign key values.
What is an attribute instance?
An individual value. Usually becoems a column value.
What are the three phases of database design?
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
What makes up a table?
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.
What is an empty table?
A table without rows.
What is a data type?
Each column has a data type, which defines the format of the values.
What are different data types?
Integer, decimal, character, time, binary, spatial, document
What structural rules must tables obey?
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)
What is data independence?
All relational databases must obey this principle, which states that rows and columns of a table have no inherent order.
What is a null value?
A special value that represents missing data. Represents either ‘unknown’ or ‘inapplicable, should not replace 0s.
What is an operator?
A symbol that computes a value from one or more other values, called operands.
What is an aggregate function?
operates on numeric values from multiple rows, including only rows selected by the WHERE clause.
Includes: SUM, AVG, MAX, MIN
What is a primary key?
A column, or group of columns, used to identify a row.
What two rules must primary keys obey?
Values must be unique within the column.
Values may not be null
What is a composite primary key?
Consists of multiple columns. A simple one consists of a signle column.
What makes up a minimal primary key?
All columns are necessary for uniqueness.
What three rules most composite primary keys obey?
Column values, when grouped must be unique
May not contain null
must be minimal
What is a foreign key?
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.
