Chapter 4

Referential Integrity as a constraint

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

Entity Integrity as a constraint

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

First Generation DBMS TIME PERIOD TYPE(S)

(early 60s) • Hierarchical • Network

Second Generation DBMS TIME PERIOD TYPE(S)

(late 60s - early 70s) • Relational model

Third Generation DBMS TIME PERIOD TYPE(S)

(late 70s - 80s) • Object-oriented DBMS

What is the difference between an entity and a table?

1.) tables often do not correspond to an entity BUT 2.) MOST entities are tables 3.) A table can represent a relationship --often Is (many-to-many) 4.) not every relationship show up as a table (One to many -->often not a table)

Base Relation

A relation whose tuples are physically stored in the database

Updating View

All updates to a base relation should be immediately reflected in all views that reference that base relation. If view is updated, underlying base relation should reflect change. There are restrictions on types of modifications that can be made through views: • Updates are allowed if query involves a single base relation and contains a candidate key of base relation. • Updates are not allowed involving multiple base relations. • Updates are not allowed involving aggregation or grouping operations.

What is the mathematical definition of a relation?

Any subset of these ordered triples is a relation -see notes for this one

Is there any significance to the order of an attribute or tuple ?

No, but it could theoretically impact the query efficiency for the tuple

Integrity Constraints for tables

Null: • Represents value for an attribute that is currently unknown or not applicable for tuple. • Deals with incomplete or exceptional data. • Represents the absence of a value and is not the same as zero or spaces, which are values. Entity Integrity • in a base relation, no attribute of a primary key can be null. Referential Integrity • if foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null. Additional General • additional rules specified by users or database administrators that define or constrain some aspect of the enterprise. Example: No staff member may have a salary that exceeds $100,000.00

which key CANNOT be null

Primary key it will cause integrity issues

What is the purpose of a view?

Provides powerful and flexible security mechanism by hiding parts of database from certain users. Permits users to access data in a customized way, so that same data can be seen by different users in different ways, at same time. Can simplify complex operations on base relations.

Talk about the keys foreign primary Talk about the descriptions for a relationship with tables rows column number of columns number of rows See IMAGE

See image

Relational database schema

Set of relation schemas, each with a distinct name. (and other things) ex.) Student (StuId: INT, LastName: STRING, FirstName: STRING, major: STRING,) Faculty (FacId: STRING, Name: STRING, Dept: DEPTS) Class (FacId: STRING, Schedule: STRING, Room: STRING, ClassNum: COURSES) Enroll (ClassNum: COURSES, StudId: DEC, Grade: GRADES) Department(DeptId: DEPTS, Name: STRING)

How would you describe the image

The Branch relation is any subset of the Cartesian product of the domains, or any set of four-tuples + the first element is from the domain BranchNumbers, + the second is from the domain StreetNames

attribute

a named column of a relation. has a distinct name values of the attribute is all from the same domain. +there is no significance to the order of the attributes +in mathematical terms, it would matter, but not here +column headings will define which attribute the value belongs to

relation

a table with columns and rows. • Only applies to logical structure of the database, not the physical structure.

Each cell of the schema contains...what?

exactly one atomic (single) value

Primary Key

it is a candidate key that was selected to identify tuples uniquely within a relation

relation name

it is distinct from all other relation names in the relational schema

cardinality

number of tuples in a relation or number of rows in a table

the table containing the candidate key is called

referenced or parent table

Database instance

set of (corresponding) relation instances

attribute domain

set of allowable values for one or more attributes ex.) character number ex.) description of what the data should be ex.) data format ie. 999-999-9999

What does the presence of a large number of nulls indicate

the relation should be decomposed into one or more subrelations

Problems with early (first generation) databases

• Navigating the records requires complex programs • There is minimal data independence • No theoretical foundations

Fourth Generation DBMS TIME PERIOD TYPE(S)

(late 80s - present) • Based on the third generation but equipped with the extended featured for: 1― Distributed, Client/Server DBS 2― Parallel DBS 3― Object-Oriented DBS/Object-Relational DBS 4― Spatial Databases for storage and retrieval of multimedia data, image data, scientific data analysis, geographic information, biomedical data, multi-attribute indexing... 5― Data warehousing,...

Alternative Key

A candidate key that was not selected as the primary key.

True or false: a view does not necessarily exist in the database, but it is produce as the result of the request at that time

True views are dynamic: means that changes made to base relations that effect view attributes are immediately reflected in the view

contents of a view are defined as

a query on one or more base relations

tuple

a row of a relation -a row in a table -distinct - no duplicates tuples -order of tuples have no significance + but order may effect the query efficiency +often we order based on 1+ attributes

Relation schema defines the following 4

a schema • Relation name • Attribute names and domains • Integrity constraints - e.g., ― The values of a particular attribute in all tuples are unique ― The values of a particular attribute in all tuples are greater than 0 • Default values

Additional General as a constraint

additional rules specified by users or database administrators that define or constrain some aspect of the enterprise. Example: No staff member may have a salary that exceeds $100,000.00

Foreign key

an attribute or set of attributes, from within one relation that matches candidate key of some (possibly the same) relation +typically denotes the relationship with another relation +The table containing the foreign key is called the child table +the table containing the candidate key is called the referenced or parent table

Super Key

an attribute, or set of attributes, that uniquely identifies a tuple within a relation.

views are dynamic: means what?

changes made to base relations that effect view attributes are immediately reflected in the view

The table containing the foreign key is called

child table

What are alternative names for a view

derived relation or virtual relation

View

dynamic result of one or more relational operations operating on base relations to produce another relation. -a view does not necessarily exist in the database, but it is produce as the result of the request at that time -the contents of a view are defined as a query on one or more base relations -views are dynamic: means that changes made to base relations that effect view attributes are immediately reflected in the view • Alternative names: derived relation or virtual relation

relational database

it is a collection of normalized relations with distinct relation names +typically, the only shared columns among relations are foreign keys --> eliminated redundancy + contains a finite set of relations + Each relation consists of a schema definition and an instance of the relation Database schema = set of relation schemas (and other things) Database instance = set of (corresponding) relation instances

Candidate key

superkey (K) such that no proper subset is a superkey within the relation. • Candidate key is the minimal set of attribute(s) which can uniquely identify a tuple.

degree

the number of attributes in a relation or the number of columns in a table

domain

the set of allowable values for one or more attributes.

Null (as a constraint)

• Represents value for an attribute that is currently unknown or not applicable for tuple. • Deals with incomplete or exceptional data. • Represents the absence of a value and is not the same as zero or spaces, which are values. • Example: In the Viewing relation, the Comment attribute might be Null if the client has not left a comment about that property • The presence of a large number of Nulls usually suggests that the relation should be decomposed into one or more subrelations (more on this later in the course)

What restriction exist on updating or modifying views

• Updates are allowed if query involves a single base relation and contains a candidate key of base relation. • Updates are not allowed involving multiple base relations. • Updates are not allowed involving aggregation or grouping operations.