What is the rule states that any foreign key value on the relation of the many side must match a primary key value in the relation of the one side?

Upgrade to remove ads

Only ₩37,125/year

1. Science
2. Computer Science

• Flashcards

• Learn

• Test

• Match

• Flashcards

• Learn

• Test

• Match

Terms in this set (25)

Logical Database Design

Process of transforming the conceptual data model into a logical data model
Creating stable database structures
It's consistent and compatible with a type of database technology (relational data model is one more of logical data model)

Relational Data Model Components

Data structure: data are organized in tables
Data manipulation: operations (using SQL) on data stored in the relations
Data integrity: mechanisms for specifying business rules that maintain data integrity when they are manipulated

Relation

A named, two-dimensional table of data.
Consists of rows (records) and named columns (attribute or field)
Corresponds with entity types and with associative entities.
Rows correspond with entity instances
Columns correspond to attributes

Relation Qualifications

It much have a unique name
Every field value on a specific row must be atomic (not multivalued or composite)
Every row must be unique (can't have two rows with the exact same values for all their fields)
Fields (columns) in tables must have unique names
The order of the columns must be irrelevant
The order of the rows must be irrelevant

Key Fields

Keys are special fields that serve as:
Primary key: a unique identifier(s) of the relation.
Foreign key: identifiers that enable a dependent relation (on the many side) to refer to its parent relation (on the one side)
Keys can be simple or composite.
Keys are usually used as indexes to speed up the response to user queries

Schema for Relations

A relational schema describes the logical structure of a relational database.

A short text statement: the name of a relation followed by the name of its attributes in parentheses... EMPLOYEE1 (EMPID, Name, DeptName, Salary)

A graphic representation: a relation represented by a rectangle containing the attributes of the relation.

Integrity Constraints

Domain Constraints: a domain is the set of values that may be assigned to an attribute. It defines allowable values for an attribute. Domain definitions enforce domain integrity constraints (character size, date format, integer digit size, description, etc)

Entity integrity: no primary key attribute may be null. All primary key fields must have data.

Referential Integrity: rule states that any foreign key value must match a primary key value in the relation of the one side. (or the foreign key can be null). The primary key of the relation of the one side becomes one attribute (the foreign key) in the relation on the many side.
An arrow is drawn from dependent to parent table

Referential Integrity

Delete Rules:
Restrict: don't allow delete of "parent" side if related rows exist in "dependent" side
Cascade: automatically delete "dependent" side rows that correspond with the "parent" side row to be deleted
Set-to-Null: set the foreign key in the dependent side to null if deleting from the parent side... not allowed for weak entities.

Foreign key an only be null if there is no mandatory minimum cardinality of the one side of relationship.

Mapping Regular Entities to Relations

1. Simple attributes map directly onto the relation's fields
2. Composite attributes use only their simple, component attributes
3. Multivalued attributes become a separate relation with a M:N relationship to the original relation... and creating an associative relation with foreign keys from both tables.

Mapping Weak Entities

Becomes a separate relation with a foreign key taken from the superior entity

Primary key composed of partial identifier of weak entity and the primary key of identifying relation (strong entity)

Binary Relationships

1:M: Primary key on the one side becomes a foreign key on the many side
M:N: create a new relation with the primary keys of the two entities as its primary key
1:1: primary key on the mandatory side becomes a foreign key on the optional side

Mapping Associative Entities

Identifier not assigned.. default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship)
Identifier assigned: it is natural and familiar to end-users, default identifier may not be unique.

If the associative relation has its own identifier, it will have the two other relations IDs as foreign keys

Mapping Unary Relationships

1:M: recursive foreign key in same relation
M:N: two relations...one for the entity type, one for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity.

Mapping Ternary Relationships

One relation for each entity and one for the associative entity
Associative entity has foreign keys to each entity in the relationship

Surrogate key

A serial number or other system assigned primary key for a relation

Used when there are many primary keys to compose a composite primary key... it would be better to just make a surrogate key and put the other keys back to regular attributes or to foreign keys

Mapping Supertype/Subtype Relationships

One relation for supertype and each subtype
Supertype attributes (including identifier and subtype discriminator) go into supertype relation
Subtype attrubutes go into each subtype, primary key of supertype relation also becomes primary key of subtype relation

1:1 relationship established between supertype and each subtype, which superype as primary table

Data Normalization

Primary tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data

The process of decomposing relations with anomalies to produce smaller, well-structured relations.

Well-structured Relations

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies.

Goal is to avoid anomalies:
Insertion anomaly: adding new rows forces user to create duplicate data
Deletion anomaly: deleting rows may cause a loss of data that would be needed for other future rows
Modification Anomaly: changing data in a row forces changes to other rows because of duplication

**A table should not pertain to more than one entity type.

Normal Form

A state of a relation that requires that certain rules regarding relationships between attributes are satisfied.

First Normal Form (1NF)

No multivalued attributes
Every attribute value is atomic

Functional Dependency

The value of one attribute (the determinant) determines the value of another attribute

Candidate key: a unique identifier... one of the candidate keys will become the primary key... Each non-key field is functionally dependent on every candidate key

Second Normal Form (2NF)

1NF plus every non-key attribute is fully functional dependent on the ENTIRE primary key.
Every non-key attribute must be defined by the entire key, not by only part of the key
No partial functional dependencies

Should only be one primary key (or one set of relating primary keys) that define a relation.... Order ID and Product ID should not be in the same relation

Third Normal Form (3NF)

2NF plus no transitive dependencies (functional dependencies on non-primary-key attributes.
This is called transitive because the primary key is a determinant for another attribute, which is turn is a determinant for a third.

Solution: Non-key determinant with transitive dependencies go into a new table. Non-key determinant becomes primary key in the new table and stays as foreign key in old table.

Merging Relations

View integration: combining entities from multiple ER models into common relations
Issues to watch out for when merging entities from different ER models:
1. synonyms: two or more attributes with different names but same meaning
2. homonyms: attributes with same name but different meanings.
3. transitive dependencies: event if relations are in 3NF prior to merging, they may not be after merging
4. supertype/subtype relationships: may be hidden prior to merging

Enterprise keys

Primary key that are unique in the whole database, not just within a single relation

Corresponds with the concept of an object ID in object-oriented systems

Recommended textbook solutions

Computer Organization and Design MIPS Edition: The Hardware/Software Interface

5th EditionDavid A. Patterson, John L. Hennessy

220 solutions

Engineering Electromagnetics

8th EditionJohn Buck, William Hayt

483 solutions

Introduction to Algorithms

3rd EditionCharles E. Leiserson, Clifford Stein, Ronald L. Rivest, Thomas H. Cormen

720 solutions

Big Java: Early Objects

5th EditionCay S. Horstmann

1,008 solutions

Sets with similar terms

IS385 Chapter 9

50 terms

nzxy

Data Mgmt - Chapter 4

20 terms

melanienicole70

Database Management Ch. 3

41 terms

Andie_Swift

Modern Database Management - Chapter 4

76 terms

ABUIC

Other sets by this creator

RevZilla Digital Marketing Co-op

6 terms

amanda_abrams8

MIS 343 Chapter 9

28 terms

amanda_abrams8

MIS 343 Chapter 8

15 terms

amanda_abrams8

MIS 343 Chapter 7

17 terms

amanda_abrams8

Verified questions

COMPUTER SCIENCE

Show that the nth harmonic number is Ω(lg n) by splitting the summation.

Verified answer

COMPUTER SCIENCE

Under what circumstances do page faults occur? Describe the actions taken by the operating system when a page fault occurs.

Verified answer

COMPUTER SCIENCE

True/False: The first parameter of a Python method definition is called this.

Verified answer

COMPUTER SCIENCE

Design and implement a class called Card that represents a standard playing card. Each card has a suit and a face value. Create a program that deals 20 random cards.

Verified answer

Other Quizlet sets

CAPM

365 terms

Karen_Wichman

Vocabulary Workshop G Units 1-9

180 terms

kernst

Agroecology Midterm

57 terms

Allen_Warren9

Bible Doctrines SG 9-A

42 terms

Samcornelll

Related questions

QUESTION

Using the database depicted above, "Unit Price" is best described as

3 answers

QUESTION

(True or False) One objective of selecting a data type is to minimize storage space.

4 answers

QUESTION

What does a completeness check accomplish?

2 answers

QUESTION

Query results made to a transaction database are dynamic. (T/F)

4 answers

Which rule states that every foreign key value must match the primary key value of a record in another table or must be null?

Which rule states that a foreign key?

Which of the following states that a foreign key can either be a value which is a primary key value of referenced table or it can have a null value?

Which of the following constraints ensures that the foreign key value in the referencing relation must exist in the primary key attribute of the referenced relation?