1 Database Systems Lecture 5
2 The purpose of the lecture • Learn to use database management software to develop data-intensive applications • Become familiar with fundamental DBMS (СУБД) concepts • Gain exposure to future trends in databases
3 Typical roles and career path for database professionals • • Data Architect Database Administrator (DBA) Application Developer
4 Illustrative Capabilities: • A. Real Estate Information System: Successful students will be able to design and create a Web-accessible database for a real estate company to keep track of their rentals, lease renewals, and such. Included in this is the ability to write applications for the users of the database such as maintenance personnel, the property manager, and others.
5 Grading policy Text book: • Thomas M. Connolly, et al. Database Systems: A practical approach to Design, Implementation, and Management. 3 rd Edition ISBN: 0201708574 Addison-Wesley, 2001
6 Agenda • Unit 1. Database Systems • 1. 1 Introduction to Databases and Systems ▫ 1. 1. 1 What is a Database? ▫ 1. 1. 2 Examples of Database Systems ▫ 1. 1. 3 When is a Database Management System Needed? ▫ 1. 1. 4 When is a Database Management System Not Needed? (Primary. Key, Alternate Key, Foreign Key)
7
8
9
10
11
12
13
14
15
16
17
18
19
20 Example • Library DBMS
21
22
23
24
25
26 Information model • An information model is an abstract, formal representation of entities that includes their properties, relationships and the operations that can be performed on them.
27 Data Model • The primary motivation behind the concept is to formalize the description of a problem domain without constraining how that description will be mapped to an actual implementation in software. There may be many mappings of the Information Model. Such mappings are called data models
28 Relationship between an Information Model and a Data Model
29 Types of information models • • • Network (CODASYL): 1970’s Hierarchical (IMS): late 1960’s and 1970’s Relational: 1970’s and early 1980’s Entity-Relationship: 1970’s Extended Relational: 1980’s Semantic: late 1970’s and 1980’s Object-oriented: late 1980’s and early 1990’s Object-relational: late 1980’s and early 1990’s Semi-structured (XML): late 1990’s to the present
30 Entity-Relationship model • The relational data model is simple and elegant. It has a solid mathematic foundation based on sets theory and predicate calculus and is the most used data model for databases today. • Peter Chen (1976) proposed thinking of a database as a collection of instances of entities. • Entities are objects that have an existence independent of any other entities in the database. • Entities have attributes, which are the data elements that characterize the entity. • One or more of these attributes could be designated to be a key. • Lastly, there could be relationships between entities. Relationships could be 1 -to-1, 1 -to-n, n-to-1 or m-to-n, depending on how the entities participated in the relationship. Relationships could also have attributes that described the relationship.
31
32 The relational data model
33 • Figure above shows the main aspects of the relational data model: • Specific relational data model concepts like attributes, tuples, domains, relations, domains, schemas, keys • The relational data model constraints like entity integrity, referential integrity, and semantic constraints which are used to enforce rules on a relational database • Relational algebra operations like union, intersection, difference, Cartesian product, selection, projection, join and division which are used to manipulate relations in a relational data model • Relational calculus which is an alternative to the relational algebra as a candidate for the manipulative part of the model
34 Basic concepts: attributes record row
35 Domains • A domain is a set of atomic values that are all of the same type. A value is the smallest unit of data in the relational model. For example, BMW, Mercedes, Audi, and VW are values for the attribute Producer. Those values are considered to be atomic, that is they are non-decomposable as far as the model is concerned. The domain for the Producer is the set of all possible car producer names. An attribute always has a domain associated with it. This domain gives the possible values for that attribute. Two or more attributes can be defined on the same domain.
36 Tuples /rows/ records • A tuple is an ordered set of values that describe data characteristics at one moment in time. In Figure above, you can see an example of a tuple. Another formal term used to define a tuple is ntuple. Informal terms used for tuples are: row in a table or record in a data file.
37 Relations • A relation is the core of the relational data. According to introduction to database systems a relation on domains D 1, D 2, …, Dn (not necessarily distinct) consists of a heading and a body. • The heading consists of a fixed set of attributes A 1, A 2, …, An, such that each attribute Ai corresponds to exactly one of the underlying domains Di (i=1, 2, …, n). • The body consists of a time-varying set of tuples, where each tuple in turn consists of a set of attribute -value pairs (Ai: vi) (i=1, 2, …, n), one such pair for each attribute Ai in the heading. For any given attribute-value pair (Ai: vi), vi is a value from the unique domain Di that is associated with the attribute Ai.
38 Relations • A relation degree is equivalent with the number of attributes of that relation. The relation from Figure above has a degree of 6. A relation of degree one is called unary, a relation of degree two binary, a relation of degree three ternary, and so on. A relation of degree n is called nary. • Relation cardinality is equivalent with the number of tuples of that relation. The relation from Figure above has a cardinality equal to 5. The cardinality of a relation changes with time, whereas the degree does not change that often.
39 Schemas • A database schema is a formal description of all the database relations and all the relationships existing between them.
40 Keys • The relational data model uses keys to define identifiers for a relation’s tuples. The keys are used to enforce rules and/or constraints on database data. Those constraints are essential for maintaining data consistency and correctness. Relational DBMS permits definition of such keys, and starting with this point the relational database management system is responsible to verify and maintain the correctness and consistency of database data. Let’s define each type of key.
41 Candidate keys • A candidate key is a unique identifier for the tuples of a relation. C. J. Date definition for a candidate key: • Let R be a relation with attributes A 1, A 2, …, An. The set of K=(Ai, Aj, …, Ak) of R is said to be a candidate key of R if and only if it satisfies the following two timeindependent properties: • Uniqueness ▫ At any given time, no two distinct tuples of R have the same value for Ai, the same value for Aj, …, and the same value for Ak. • Minimality ▫ None of Ai, Aj, …, Ak can be discarded from K without destroying the uniqueness property.
42 Candidate keys
43 Primary keys • it is a candidate key that is chosen to represent the relation in the database and to provide a way to uniquely identify each tuple of the relation. A database relation always has a primary key.
44 Foreign keys • A foreign key is an attribute (or attribute combination) in one relation R 2 whose values are required to match those of the primary key of some relation R 1 (R 1 and R 2 not necessarily distinct). Note that a foreign key and the corresponding primary key should be defined on the same underlying domain.
45
How are databases used? • Computer databases allow users to ▫ ▫ ▫ Add, Edit, and Delete records; Extract records using specific criteria; and to Extract aggregate data from collections of records • Database transactions are accomplished through queries
Database queries • SELECT queries are used to extract information from a database: Example: SELECT first_name, last_name FROM president WHERE state = ‘NY’;
More database queries • More examples SELECT first_name, last_name FROM president WHERE birth_date like ’ 19%’; SELECT count(*) FROM president WHERE state = ‘VT’; SELECT first_name, last_name FROM president WHERE state IN (ME, NH, VT, MA, CT, RI);
How to create a DB table? • Use CREATE TABLE query, specifying fields and column types: Example: CREATE TABLE friends ( first_name varchar(25), last_name varchar(25), gender enum(‘M’, ‘F’), grade smallint unsigned, hair_color varchar(20), email varchar(30), screen_name varchar(25), phone char(12));
Adding records to a table • Use the INSERT query: Example: INSERT INTO friends VALUES (‘Daffy’, ‘Duck’, ‘M’, 11, ’Brunette’, ‘daffy@disney. com’, Yellow. Bill’, ’ 315 -555 -1213’);
Editing records • Use the UPDATE query: Example: UPDATE friends SET phone=‘ 315 -555 -1234’ WHERE first_name=‘Daffy’ and last_name = ‘Duck’;
Deleting records from a table • Use the DELETE query: Example: DELETE FROM friends WHERE first_name=‘Daffy’ and last_name=‘Duck’;
How to use a DB table? • Finding a date: SELECT first_name, screen_name, phone FROM friends WHERE gender=‘F’ and grade > 10 and hair_color=‘Red’;
What are relational databases?
Relations, primary keys, joins id name 2359 Humphrey Bogart id title 1969 Casablanca 1920 The Big Sleep movieid actorid ord 1969 2359 1 1920 2359 1
Querying with JOIN SELECT name FROM casting JOIN actor ON casting. actorid=actor. id JOIN movie ON casting. movieid=movie. id WHERE actor. name = ‘Humphrey Bogart’
