The Relational Model
COMP 421 @ McGill 1
Database Models
Copyright By PowCoder代写 加微信 powcoder
• Theentity-relationshipisasemanticmodel(quite rich in its expressiveness) but it is not used as a model in any DBS.
• Relationalmodel
– Most common
– Vendors: IBM DB2, Microsoft SQL Server, Oracle, SAP Hana, SAP Sybase, etc…
– Open-source: PostgreSQL, MySQL, SQLite, Derby, MonetDB,
• “Legacysystems”haveoldermodels
– E.g., IBM’s IMS (hierarchical), CODASYL network
COMP 421 @ Mc Models
• Recent and future competitors:
– object-oriented model: ObjectStore,Versant
– Semi-structured (document-based): XML, JSON • MongoDB,CouchDB
– Key-value
• Riak, Voldemort, Amazon’s Dynamoth
– Column-store (key – basic relational)
• Google’s BigTable, Hadoop’s Hbase, Cassandra
– Integrated: object-relational, XML+relational …
COMP 421 @ Mc Engines in Use
https://db-engines.com/en/ranking_categories
COMP 421 @ McGill 4
Database Engines in Use
https://db-engines.com/en/ranking_categories
COMP 421 @ McGill 5
Database Engines in Use
https://db-engines.com/en/ranking_categories
COMP 421 @ McGill 6
Definitions
• Relational Database: a set of relations
• Relation: Consists of two parts:
– Schema: specifies name of relation, plus a set of attributes, plus the domain/type of each attribute
• E.g.,Students(sid:int, name:string, login:string, faculty:string, major:string)
– Instance: set of tuples (all tuples are distinct).
– Compare with entity set and entity; or with object class and object
• A relation can be seen as a table:
– Columnheaders=attributenames,rows=tuples/records,columns/fields = attribute values
• If clear from context, we say instead of “instance of a relation” simply “relation”
• Database Schema: collection of relation schemas
COMP 421 @ Mc of Students Relation Column headers = attributes
Software Engineering
Software Engineering
Computer Science
• All rows are distinct (set-oriented)
• Rows are not ordered (a permutation of rows represents still the
same table)
• Columns are per definition not ordered but in practice we often assume a fixed order
– with this, a single tuple can be represented as
• (53666, Bartoli, Science, Software
Engineering)
COMP 421 @ Mc DDL and DML
• Data Definition Language (DDL): defines the schema of a database
• Data Manipulation Language (DML): “manipulates” the data, i.e., the instances of the relations
– Insert, update, delete tuples
– “Query” the relations: retrieve tuples that fulfill certain criteria
(hence, often called “query language”)
– The Relational Model offers simple and powerful querying of data
with precise semantics – Data-centric
• Physical data independence
– User only sees relations
– User does not need to know how data is stored
• Mapping to files
• Rows vs. columns
– User does not need to know how queries are executed COMP 421 @ McGill
The SQL Query Language
• Developed by IBM (system R) in the 1970s
• Need for a standard since it is used by many vendors
• Standards: – SQL-86
– SQL-99 / SQL3 (adds object-relational features) – SQL:2003 (adds XML features)
– SQL:2011
• Used to define relations (Data definition)
• Used to write and query data (Data manipulation) • Standard is very slow to develop
• Many dialects in various DBS
10 COMP 421 @ Mc vs. Details
• We will discuss the main concepts of SQL (data definition and data manipulation) in class
• To figure out the details, students have to use the handbook
– In particular: DBS specific dialects, variations…
COMP 421 @ Mc QL Data Types
• All attributes must have a data type.
• SQL supports several basic data types
– Just as any programming language does
– Notation somewhat “old-fashioned” as language very old
• Character and string types
– CHAR(n) denotes a character string of fixed length (containing trailing
blanks for padding if necessary).
– VARCHAR(n) denotes a string of up to n characters (between 0 and n characters).
– SQL permits reasonable coercion between values of character-string types
• Integer Types
– INT or INTEGER (names are synonyms) – SHORTINT
COMP 421 @ Mc Types (contd.)
• Floating point numbers
– FLOAT or REAL (names are synonyms)
– DOUBLE PRECISION
– DECIMAL(n,d): real number with fixed decimal point.Value consists of n digits,
with the decimal point d positions from the right.
• Dates and time:
– DATE:hastheform‘YYYY-MM-DD’
– TIME:hastheform‘15:00:02’or‘15:00:02.5’ – May be compared and converted to string types
• Bit strings
• User defined domains
– New name for a data type
– Possibility to define restrictions on values of domain (< 10)
COMP 421 @ Mc Definition: Table Creation
• Definesallattributesoftherelation
• Thetype/domainofeachattributeisspecified
• DBMSenforcecorrecttypewheneveratupleisaddedor modified
• SQLiscaseinsensitive
• Itispossibletodefinedefaultvalues
CREATE TABLE Students
(sid INTEGER,
name VARCHAR(30),
login VARCHAR(30),
faculty VARCHAR(20),
major VARCHAR(20) DEFAULT‘undefined’) COMP 421 @ Mc Manipulation: Insert
COMP 421 @ McGill 15
Data Manipulation: Insert
• Insert a single tuple
INSERT INTO Students
VALUES (53666, ‘Bartoli’,
‘Science’, ‘Software Engineering’)
– can contain all or only a subset of attributes: • Not indicated attributes have special NULL value
INSERT INTO Students (sid,name,faculty)
VALUES(53688, ‘Chang’, ‘Eng’)
INSERT INTO Students (sid,name,major)
VALUES (53650, ‘Chang’, ‘Computer Science’)
Software Engineering
53650 NULL Computer Science
COMP 421 @ Mc Manipulation: Delete
• Can delete all tuples satisfying some condition
FROM Students
WHERE name = ‘Chang’
Software Engineering
Computer Science
COMP 421 @ Mc Manipulation: Update
• Can update all tuples satisfying some condition UPDATE Students
SET major = ‘Software Engineering’ WHERE sid = 53688
Software Engineering
Eng SoftNwUaLrLe Engineering
Computer Science
COMP 421 @ Mc the Data
• Find the names and major of all students in the
Faculty of Science
SELECT name, major
FROM Students
WHERE faculty = ‘Science’
Software Engineering
Computer Science
Software Engineering
Computer Science
Much more of that COMP 421 @ McGill in a few weeks...
Data Definition: Destroying Relations
DROP TABLE Students
• DestroystherelationStudents.Theschema information and the tuples are deleted.
COMP 421 @ Mc Definition: Altering Tables
DROP TABLE Students
• DestroystherelationStudents.Theschema information and the tuples are deleted.
ALTER TABLE Students
ADD COLUMN firstYear:integer
The schema of students is altered by adding a new field;
every existing tuple in the current instance is
21 extendedwithanullvalueinthenewfield. COMP 421 @ Mc Constraints (ICs)
• Integrity Constraints must be true for any instance of the database;
– Domain definitions (INT or FLOAT) are already a form of constraint
– Database designer specifies ICs when schema is defined
– DBMS checks whether ICs remain true whenever relations are modified.
• If modification violates an IC, the DBMS disallows the modification (throws an error message)
– Of course, DBMS can only check what is specified in the schema
COMP 421 @ Mc Null
CREATE TABLE Students
(sid CHAR(9),
name VARCHAR(30) NOT NULL,
login VARCHAR(30),
faculty VARCHAR(20),
major VARCHAR(20)
DEFAULT ‘undefined’) requires an attribute to always have a
proper value
COMP 421 @ Mc Key Constraints
• A set of fields is a key candidate for a relation if
– No two distinct tuples can have same values in all key fields, and
– This is not true for any subset of the key.
• Minimum subset of attributes that fulfill uniqueness property
• If there are two or more keys, one of the candidates is chosen to be the primary key.
• The primary key attributes of a tuple may not be NULL.
• E.g. sid is a key for Students. (What about name?).
• Example of combined keys:
– Location(building, roomNo, capacity)
– Attributes building and roomNo together build the primary key
COMP 421 @ Mc and Candidate Keys in SQL
• Possibly many candidate keys exist, one of which is chosen as the primary key
– Each student has a unique id.
– Each student has a unique login
CREATE TABLE Students
(sid CHAR(9) PRIMARY KEY,
login VARCHAR(30) NOT NULL UNIQUE,
name VARCHAR(20),
• Defining primary keys that have more than one attribute
CREATE TABLE Location
(building VARCHAR(20),
roomNo INT,
capacity INT,
PRIMARY KEY(building, roomNo)
COMP 421 @ Mc e
• Specificsofindividualdatabasesystems
– E.g., DB2 required the following
• If primary key, then also NOT NULL must be defined
– If there are error messages
• Consult the handbooks of the resp. database system
• Multi-Set
– Tables do not require to have a primary key
– If there is no primary key
• Two records/tuples can have the same value in all attributes • That is, duplicates are allowed
– Different to Relational Algebra Assumptions
• Relations are setàno identical tuples in a relation
COMP 421 @ McGill 26
Topology112
Topology112
History105
Foreign Key
• Foreign Key: Set of attributes in one relation R that is used to “refer” to a tuple in another relation Q.
– Must correspond to the primary key of the second relation Q.
– Represents a “logical pointer”.
• Examples
– inrelationEnrolled,
• sid is a foreign key referring to Students
COMP 421 @ Mc Integrity
• Foreign Key Constraint: the foreign key value of a tuple must represent an existing tuple in the referred relation
– A tuple with this value in the primary key must exist in the referred relation
– Enrollment may only contain a tuple referring to a student who exists in the Students relation
• If all foreign key constraints are enforced, referential integrity is achieved, i.e., no dangling references
COMP 421 @ McGill
Topology112
Topology112
History105
Foreign Keys in SQL
Only students listed in the Students relation should be allowed to enroll for courses
CREATE TABLE Enrolled
sid CHAR(9),
cid VARCHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students, FOREIGN KEY (cid) REFERENCES Courses
COMP 421 @ McGill 29
Enforcing Referential Integrity: Default
An Enrolled tuple with a sid is inserted but no tuple with this sid exists in Students
– Disallow insertion
A Students tuple is deleted
– DisallowthedeletionofaStudentstupletowhichEnrolled tuples point
Other options: – cascade
COMP 421 @ McGill
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com