CS代考 COMP 421 @ McGill 1

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