BEFORE THE RELATIONAL MODEL– SOME HISTORY
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
Copyright By PowCoder代写 加微信 powcoder
• List 2 of the models that were used before the relational model to build databases
THE PROBLEM:
• Now that we have figured out how to represent our data as a simple paper MODEL (the ER Diagram)….. How do we translate that ER model to a REAL working database on a computer?
CS3319 9/19/19
A LITTLE HISTORY… • The Hierarchical Model
• The Network Model
HIERARCHICAL DATA MODEL
• Most popular DBMS using this model is IBM’s IMS (STILL BEING USED!)
• Based on 2 main concepts: records and parent-child relationships. Records are grouped into record types
• A parent-child relationship type is a 1:M relationship between 2 record types
• An occurrence (instance) of a PCR is 1 parent record type and CS319 a number of records from the child type 9/19/19
CS319 Question: Can you see where there will be data 9/19/19 redundancy (a problem)?
WHAT IS THE PROBLEM… • with the Hierarchical Model?
CS3319 9/19/19
Rules for Mapping ER Diagram to Hierarchical Model:
• In Hierarchical Models only 1:N representations can be modeled so for M:N relationships represent them as if they were 1:N relationships, and use pointers to save on duplication of data
Has problems modeling the following situations:
• M:N relationships
• The case where a record type participates as child in more than 1 PCR type
• N-ary relationships with more than 2 participating types
NETWORK DATA MODEL
• Designed and created by the CODASYL (Conference on Data System Languages) committee in 1971
• Often called the CODASYL Model (popular DBMS using this model is IDMS)
• Two basic data structures: Records and Sets
• Data stored as Records, Records as classified into Record Types (Record Types have a name and format for each data item)
• Set type is a 1:N relationship between 2 record types. Each set type consists of 3 elements
• A name for the set
• An owner record type • A member record type
• A record type can participate as an owner or member in any number of set types
• Uses circular linked lists
This model allows us to do things such as:
•Given an owner record, find all member records of the set occurrence
•Given an owner record, find the first or nth, or last record of that set occurrence •Given a member record, find the next or previous member record
•Given a member record, find the owner record 9/19/19
CS319 9/19/19
• Rules for Mapping an ER diagram to Network Model
• Step 1: Regular Entities –> Create a record type
• Step 2: Weak Entities –> Create a record type
• Step 3: One to One and One to Many –> Create a set type relating record type S1 to S2. For one-to-one arbitrarily choose one of S1 or S2 as owner, for one-to- many choose make the owner be the One side of the One-to-Many relationship
• Step 4: Many to Many –> Create a linking type X and make it the member record type in the 2 set types.
RELATIONAL DATA MODEL
•Introduced by Codd in 1970
•Most DBMS are based on this model
•Represents the database as a collection of relations.
•Each relation is a table: a table is a set of rows, where each row in the table represents a collection of related data values
THE IMPORTANCE OF KEYS IN RELATIONS
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Explain the importance of key attributes in relations (tables)
• Given a relation/table, identify if the key attribute(s), if it exists.
• Determine if the insertion of a new tuple will cause a key constraint to fail.
• Identify the following types of keys: Primary Key, Foreign Key, Candidate Key, Super Key
KEYS AND KEY CONSTRAINTS
• Key(s)àcombination of attributes (or a single attribute) that
can be used to enforce that no 2 tuples can be identical
QUESTION: What is the key of this table?
SSN LastName FirstName
Answer: Likely just SSN but it could also be:
• SSN and FirstName OR
• SSN and LastName OR
• SSN and FirstName and LastName
NOTE: Could NOT be just LastName OR just FirstName or LastName and FirstName
QUESTION: How about this table?
No key, nothing makes the rows unique!
LastName AND FirstName would make each row unique, so key could be:
LastName, FirstName
LastName FirstName
Simpitshoenrs
QUESTION: What if we changed the last record to have a last name of Smithers rather than Simpson, then what could be a key in this particular snapshot of the database? (although, not a good one!)
QUESTION: How about this table?
Attribute1 Attribute2 Attribute3
Answer: Could beà
• Attribute1 and Attribute2 OR
• Attribute2 and Attribute3 OR
• Attribute1 and Attribute2 and Attribute3
• SuperKey: any set of attributes that enforce that no tuples are alike • Candidate Key: sometimes a table will have 2 possible things that
could be keys (e.g. employee number and SSN, each of them is a
candidate key )
SSN LastName FirstName
34 must be a superkey such that Marge superkey
56 Simpson
relation. Candidate key
within the
QUESTIO superkey
is a “minimal superkey” that is a
78 Smithers Marge
N: Give an example of a set of attributes
for the previous table above but not a ca
ndidate key:
Attribute1 Attribute2 Attribute3
• Primary Key: Pick one candidate key to identify tuples in the relation (signify this key by underling it), could turn out to be
composite key (2 or more attributes combined together) a1 b2 c1
• Foreign Key: An attribute or set of attributes within one relation a2 b1 c2
proper subset is a
that matches the candidate key of some other (possibly the same)
relation a2 b2 c2 CS319
QUESTION: What are foreign keys in this situation: Department
DeptID DeptName MgrEmpID MgrStartDate
EmpID LastName FirstName DeptID Sex
For the following table:
LicenseNum OR
QUESTION: Give 3 Superkeys: EngineSerialNum OR LicenseNum and Make
QUESTION: Give 2 Candidate Keys:
LicenseNum EngineSerialNum
QUESTION: Give 1 Primary Key:
EngineSerialNum
LicenseNum EngineSerialNum Make Model Year
Ont ABBC 123
Ont XED 444
Que ABC 123
Que ABD 111
Ont ABCD 111
KEY CONSTRAINTS
• Keys MUST be UNIQUE
• Primary Key must be NON NULL (Also called the Entity Integrity Constraint) • Most DBMS enforce both of the above constraints
Both of the above constraints (and domain constraints) are on individual tables (just one table).
There are also constraints (in the next topic) are on relationships between tables.
CS319 9/19/19
TERMINOLOGY FOR RELATIONS AND THE RELATIONAL DATABASE
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Define the following terms that are used when describing relational databases: Domain, Relation, Table, Attribute, Column, Row, Tuple
• Given two or more sets of values over given domains, give the Cartesian Product of the sets.
• A Domain D is a set of atomic values, Example: USA_Phone_Numbers, Employee_Ages, Department IDs
• Set of 10 digit phone numbers valid in Canada
• Possible ages of employees
• A data type is specified for each domain like 10 char string or positive integer
• A Relation Schema R denoted by R(A1, A2, …An) is made up of a relation name and a list of attributes. Each attribute Aj is the name of a role played by some domain D in the relation schema R.
• A Relation (or relation state)
• r of a relation schema R(A1, A2, …. An) also denoted by r(R) is a set of n-tuples r = {t1, t2, … tm}. Each n-tuple t is an order list of n values t=< v1, v2, ... __,v__n__> , where each value vi, 1<=i<=n, is an element of dom(Ai) or a special null value.
• R is called the name of the relation schema
• Attribute is a named column in a relation schema
• Tuple is a row of a relation
• Degree of a relation is the number of attributes it contains • Cardinality of a relation is the number of tuples it contains
Tuple and Cardinality of the Relation
Attribute and Degree of the Relation
Domain for this Attribute is integers
t2 is <“The Hunger Games”, B765, ” ”, 2008, “Scholastic Press”>
Domain for this Attribute is Letter followed by 3
Relation Schema: would be integers
Book(Title, ISBN, Author, YearPub, Pub) Relation (Relation State) would be
Attribute is a column {t1,Ttu2,ptl3e, ti4s}awhreorwe:
In this case R is Book (the name of the table or relation) t1 is <“To Kill A MockTinigtblierd”,iAs78a1n,”HarptetrrLiebe”u, 1t9e60, “Warner Books”>
Tuple 1 is <“To Kill A Mockingbird”, A781,” ”, 1960, “Warner Books”>
ISBN is an attribute
Cardinality of Relation Book is 4
t3 is <“ and the Sorcerer’s Stone”, B123, ”J.K. Rowlings”, 1997, “Scholastic Press”>
Degree of the Relation Book is 5
t4 is <“All the Light We Cannot See”, A777, ” ”, 2014, “Scribner”>
YearPublished
To Kill A Mockingbird
Warner Books
The Hunger Games
Scholastic Press
and the Sorcerer’s Stone
J.K. Rowlings
Scholastic Press
All The Light We Cannot See
•Mathematical Relations:
•Suppose we have 2 set D1 = {2,4} and D2 = {1,3,5}. The Cartesian product D1 X D2 represents all possible ordered pairs:
•{(2,1), (2,3), (2,5), (4,1), (4,3), (4,5)}
Any subset of D1 X D2 represents a relation à
R = {(2,5), (4,1)}
•We could have three sets: D1, D2 and D3 and build a relation on D1 X D2 X D3
A Relation R is any possible
R2 could be:
combinations of these tuples,
{(Simpson, Homer, 40), for example:
(Simpson, Ned, 30),
R1 could be: (Flanders, Ned, 40),
{(Simpson, Homer, 40), (Flanders, Homer, 40),
(Simpson, Ned, 30), (Smithers, Ned, 40) }
(Flanders, Homer, 40), }
QUESTION: If we have:
• D1= {Simpson, Flanders, Smithers}
• D2 = {Homer, Ned}
• D3 = {40,30}
What would D1 X D2 X D3 give us: D1 X D2 X D3 is:
{(Simpson, Homer, 40), (Simpson, Homer, 30), (Simpson, Ned, 40), (Simpson, Ned, 30), (Flanders, Homer, 40), (Flanders, Homer, 30), (Flanders, Ned, 40), (Flanders, Ned, 30), (Smithers, Homer, 40), (Smithers, Homer, 30), (Smithers, Ned, 40), (Smithers, Ned, 30)}
• Relation à table
• Attribute à column
• Tuple à row
• Cartesian Product à all POSSIBLE tuples that can be produced with all possible attributes over the whole domain for each attribute.
PROPERTIES OF RELATIONS
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to: • Identify at least 5 properties of relations
• Identify mistakes in relations that make the relation(s) invalid
PROPERTIES OF RELATIONS:
• Each relation name is
• Each cell in a relation contains 1 atomic value à Normalized,
First Normal Form
• Each attribute name within a table is
• The values of an attribute are from the same domain
• The order of the attributes has no significance
• Each tuple is distinct (no duplicates)
• The order of the tuples has no significance (Tuples in a relation do not have any particular order, however in a file records are physically stored on disk so there is always an order among records. Note: we may chose to display the records in a particular order) 9/19/19
This database, see a problem?
THINGY_TABLE:
This relation, see a problem?
This relation, see a problem?
This relation, see a problem?
THINGY_TABLE2:
DEPARTMENT:
XThingy YThingy ZThingy
Arreeththeesseerreelalatitoionnssththeessaamee??
YThingy XThingy YThingy
DeptID Name Location
SSN FirstName LastName Department Position
Apple Dog 77
Bird Apple 77 CS Computer Science
TTH HI NI NG GYY__TTA ABBLLEE11: :
Computer Science Orange Cat 77
Cat Orange 77 Ma Mathematics
Apple Dog 77
TTH HI NI NG GYY__TTA ABBLLEE22: :
TThhininggyy YDTTohhgininggyy AZpTTphhlieninggyy 77
SA Statistics and Actuarial Sciences
Science Professor Orange Pig 77
YXTThhininggyy
XYTThhininggyy ZTZhTinhginygy
Biology Math
77EMPLOYEE: 77
BAirpdple ofessor
SSN FirstName LastName
DAopgple ADpopgle
POigrange OPrigange Simpson
ABpirpdle BG, NCB
• MOST OF THE PROPERTIES ARE FROM MATHEMATICAL RELATIONS
• Since a relation is a set, the order doesn’t matter, therefore the order of the
tuples doesn’t matter.
• In a set, no elements are repeated, therefore tuples are unique
• Mathematical Relations are not necessarily normalized (reduced redundancy) however Codd chose Relations to be.
• In a relation, possible values for a given position are determined by the set or domain on which the position is defined, thus in a table the values in a column must come from the same domain.
Relation (or Table)
Attribute: (there are 5 attributes in this table)
SSN FirstName LastName Department Position
Computer Science
Computer Science
Key (each tuple must be different)
Tuple (there are 4 tuples in this table)
Domain Sample Domain: domain of SSN is 000 to 999 in this table
Introduction to SQL Data Definition Language
Data Definition Language
• The SQL data-definition language (DDL) allows the specification of information about relations, including:
– The schema for each relation. – The domain of each attribute. – Integrity constraints
– Other information such as
• The set of indices to be maintained for each relations.
• Security and authorization information for each relation. • The physical storage structure of each relation on disk.
CISC 332 / CMPE 332 2
Creating a Database
• A database is a collection of tables (relations).
• Before you can create relations, you need to create a database.
create database myDatabaseName
CISC 332 / CMPE 332 3
Creating Relations
• We will specify for each relation: – relation name
– attributes, domains
– constraints:
• primary key
• foreign key(s) • null/not null
– (there are other things you can specify, but
these are the basics)
CISC 332 / CMPE 332 4
Some Basic MySQL Domain Types
• char(n). Fixed length character string, with user-specified length n.
• varchar(n). Variable length character strings, with user-specified
maximum length n.
• int. Integer (a finite subset of the integers that is machine-dependent).
• decimal(p,d). Fixed point number, with user-specified precision of p
digits, with d digits to the right of decimal point.
• date. a date in YYYY-MM-DD format
• time. stores the time in HH:MM:SS format
• enum. a list – used for specifying a value can be “A”, “B” or “C”
• there are many others!!!
CISC 332 / CMPE 332 5
Char vs Varchar
• char(10) – store “cat” – 7 spaces are appended to the string to make it 10 characters long
• varchar(10) – “cat” is stored as 3 characters. Maximum # of characters is 10 for any value.
CISC 332 / CMPE 332 6
Create Table/Insert Statements create table instructor (
varchar(20) not null, varchar(20),
decimal(8,2))
insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000.29)
ID name dept salary
CISC 332 / CMPE 332 7
Integrity Constraints
• Integrity constraints ensure changes made to a database do not result in data inconsistency
• Eg. not null primary key (A)
foreign key (B) references T (C)
CISC 332 / CMPE 332 8
Integrity Constraints
create table instructor(
instructorID char(5) not null primary key, name varchar(40),
salary decimal(10, 2))
create table teaches (
instructorID char(5) not null,
courseNum varchar(5) not null,
primary key (instructorID, courseNum),
foreign key (instructorID) references instructor (instructorID));
CISC 332 / CMPE 332 9
Foreign Keys
• When defining a foreign key, the referenced table must already exist.
– if this is not possible, you may need to create the table without the foreign key and use an “alter table” statement to add the foreign key later.
CISC 332 / CMPE 332 10
Foreign Keys – on delete cascade
create table instructor (
ID char(5) not null, name varchar(20) not null, dept_name varchar(20),
salary numeric(8,2), primary key (ID),
foreign key (dept_name) references department (dept_name) on delete cascade
on delete specifies what happens to the tuples in instructor if the referenced tuple is deleted:
cascade – the child tuple is deleted (so, if the associated department for an instructor is deleted, all instructors associated with that dept are also deleted.
CISC 332 / CMPE 332 11
333 444 777
332 499 332
Name Address
Wendy 99 Pine St Thomas 43 Mark 10
1994-10-01 1997-05-14 1994-03-01
fk “on delete cascade” **
333 92 333 57 777 92
**eg. if student 333 is deleted from Students, all records for
student 333 in grades are also deleted.
CISC 332 / CMPE 332 12
Foreign Keys – on delete set null
create table instructor (
ID char(5) not null, name varchar(20) not null, dept_name varchar(20),
salary numeric(8,2), primary key (ID),
foreign key (dept_name) references department (dept_name) on delete set null
on delete specifies what happens to the tuples in instructor if the referenced tuple is deleted:
set null – the child tuple’s department value is set to null. So, if the department is deleted from the department table, the instructor remains, but has “null” for the department name.
CISC 332 / CMPE 332 13
Instructor
fk “on delete set null” **
Department
DeptNumber
Psychology
**eg. if a department is deleted, the Dept No will be set to NULL
CISC 332 / CMPE 332 14
Foreign Keys – on delete restrict
create table instructor (
ID char(5) not null, name varchar(20) not null, dept_name varchar(20),
salary numeric(8,2), primary key (ID),
foreign key (dept_name) references department (dept_name) on delete restrict
on delete specifies what happens to the tuples in instructor if the referenced tuple is deleted:
restrict – the deletion of the department will be rejected if there are instructors that are associated with that department.
This is the default if no constraint is specified.
CISC 332 / CMPE 332 15
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com