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.