Database Design Quiz 2

Many to many relationsips

*..* 0..* on one side to show each property is advertised in 0 or more newspapers 1.. * on other side to show each newspaper advertises one or more properties

Properties of relations

- distinct name - each cell contains exactly one value (normalized) - attributes and tuple names are distinct - order of attributes and tuples are not significant - values from an attribute are from the same domain

To represent that a member of staff can manage zero or one branch

0..1

Base relation

A named relation corresponding to an entity in the conceptual schema, whose tuples are physically stored in the database.

Relationship type

A set of meaningful associations among entity types. Given a name that describes its function. Represented with the name over a line connecting entity types. Labelled in one direction with a triangle symbol.

AND

A superclass member may be a member of more than one subclass

MANDATORY

A superclass member must be a member of a subclass

OPTIONAL

A superclass member need not be a member of any subclass

Relationship occurence

A uniquely identifiable association that includes one occurrence from each participating entity type.

Entity occurence

A uniquely identifiable object of an entity type.

General constraints

Additional rules specified by users or database administrators of a database that define or constrain some aspect of the enterprise.

Relational model

All data is logically structured within relations (tables). Contains attributes and tuples.

Determinant

Attribute on the left hand side of the arrow of a functional dependency

Foreign key

Attribute or set of attributes within one relation that matches the candidate key of some (possibly the same) relation. Helps link tuples in different relations.

Superkey

Attribute that uniquely identifies a tuple. May contain additional attributes that are not necessary for unique identification

SQL data types

CHAR - fixed length strings VARCHAR(20) - variable length strings INT DECIMAL(9,2) - 2 digits to right of decimal point ENUM(option 1, option 2) - must be one of the given options

Primary key

Candidate key that is selected to identify tuples uniquely within the relation. Relation always has a primary key.

Primary key (ER)

Candidate key that is selected to uniquely identify each occurrence of an entity type.

Alternate keys

Candidate keys not selected to be the primary key.

Relational database

Collection of normalized relations with distinct relation names.

Attributes

Columns of data

Ternary, quaternary

Complex relationships of third and fourth degrees, represented by a diamond with the name inside

Simple attribute

Composed of a single component with an independent existence

Composite attribute

Composed of multiple components, each with an independent existence.

Integrity rules

Constraints that apply to all instances of the database. Include entity and referential integrity.

Participation

Determines whether all or only some entity occurrences participate in a relationship. Minimum values for multiplicity ranges

Subclass

Distinct subgrouping of occurrences of entity type

Participants

Entities involved in a particular relationship.

Type hierarchy

Entity and its subclasses and their subclasses etc Known by specialization, generalization, and IS-A hierarchies. For example Manager is a specizliation of Staff.

Superclass

Entity type that includes one or more distinct subgroupings of its occurrences. Eg Staff is the superclass of Manager, SalesPersonnel, Secretary subclasses

Weak entity type

Existence dependent on some other entity type

Deletion anomalies

Exists when certain attributes are lost because of the deletion of other attributes.

Representation of composition

Filled in diamond shape

Functional dependency

For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A ® B), if each value of A is associated with exactly one value of B.

Domain constraints

Form restrictions on the set of values allowed for the attributes of relations.

Entity type

Group of objects with the same properties, identified by the enterprise as having an independent existence. Identified by a name and a list of properties. Represented as a rectangle labeled with the name of the entity (eg: Staff, PropertyForRent).

Entity-Relationship(ER) model

Identifies entities and relationships between the data that must be represented in the model.

Transitive dependency

If A R B R C then C is transitively dependent on A via B.

Full functional dependency

If A R B, B is functionally dependent on A but not on any subsets of A

Referential integrity

If a foreign key exists in a relation, the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null.

Modification anomalies

If one attribute is modified must update the tuples appropriately to avoid inconsistencies.

Entity integrity

In a base relation, no attribute of a primary key can be null.

Cardinality

Maximum values for multiplicity ranges

One to many relationships

Member of staff can oversee zero or more properties. Represented by 0:* if max is unknown

Candidate key (ER)

Minimal set of attributes that uniquely identifies each occurrence of an entity type

Strong entity type

Not existence-dependent on some other entity type

Multiplicity

Number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship Represented by min..max

Degree

Number of attributes in a relation.

Degree

Number of participants in a relationship type

Cardinality

Number of tuples in a relation.

Semantic net

Object-level model that is highly detailed and more complicate than ER model.

Insertion anomalies

Occurs when certain attributes cannot be inserted into the database without the presence of other attributes

Representation of aggregation

Open diamond shape at one end of the relationship line next to the entity that represents the whole

Specialization

Process of maximizing differences between members of an entity by identifying their distinguishing characteristics. Top-down approach that defines a set of superclasses and their related subclasses.

Generalization

Process of minimizing differences between entities by identifying their common characteristics. Bottom-up approach that results in the identification of a generalized superclass from the original entity types.

Attribute (ER)

Property of an entity or a relationship type.

First Normal Form 1NF

Relation in which the intersection of each row and column contains one and only one value

Third Normal Form 3NF

Relation that is in first and second normal form and in which no non-primary-key attribute is transitively dependent on the primary key. Reduces update anomalies

Second Normal Form 2NF

Relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key. Involves removal of partial dependency. Less redundancy than 1NF

Update anomalies

Relations that have redundant data. Include insertion, deletion, or modification aomalies.

Recursive relationship

Relationship type in which the same entity type participates more than once in different roles. Relationships may be given role names to indicate the purpose that each participating entity type plays in a relationship.

Aggregation

Represents a "has-a" relationship between entities where one represents the whole and the other the part

Null

Represents a value for an attribute that is currently unknown or not applicable for this tuple.

Tuples

Rows of one data

Diagrammatic representation of attributes

See pic

Conceptual model

Set of all schemas for the database

Domain

Set of allowable values for one or more attributes. Allows the user to define the meaning and source of values that attributes can hold in a central place.

Composition

Specific form of aggregation that represents an association between entities where there is strong ownership and coincidental lifetime between whole and part

Candidate key

Superkey where no proper subset is a superkey within the relation (aka given a value, can only determine at most one tuple)

Relation

Table of data used to hold information about the objects to be represented in the database

Conceptual database design

To build the conceptual representation of the database, which includes identifications of the important entities, relationships, and attributes. Independent of physical considerations.

Physical database design

To decide how the logical structure is to be physically implemented (as base relations) in the target DBMS. Describes implementation of database (file organizations, indexes, integrity constraints).

1:1 relationship

To represent a branch that always has one manager Represented as 1..1

Logical database design

To translate the conceptual representation to the logical structure of the database, which includes designing the relations but not the particular DBMS

Role name

Used to indicate purpose that each participating entity type plays in a relationship. Also used when two entities are associated through more than one relationship.

Composite key

When a key consists of more than one attribute (aka given a value, can determine more than one tuple)

OR

When a superclass member is a member of only one subclass

Fan trap

Where a model represents a relationship between entity types but the pathway is ambiguous. Fix by restructuring model to represent the correct association between entities.

Chasm traps

Where a model suggests the existence of a relationship between entity types but the pathway does not exist. Fix by identifying the missing relationship