Relational DBMS

1. Relational DBMS • Motivating Questions • From last time: Is MS Excel a DBMS? • What makes some databases relational, while others are not? • Why do so many people use them?

2. RDBMS: Basic Principles • Based on a variant of n-ary relations • Relations are the main data structure • A relational database consists of 1 or more relations • Connections and relationships represented by values of data, not by physical references • Reference by value • Reference by location • Natural representation as tables

3. Relations: Definitions • A relation R is a set of n-tuples <d1,d2 …,dn> • di is a domain • may be infinite and may be non-unique • all domains contain the NULL value. Null value represents either attributes whose value is not known, or do not exist • n is the degree of the relation • The number of tuples in a relation is the cardinality of the relation

4. Relations: Definitions and Properties • Because R is a set of n-tuples: • the ordering of the n-tuples in R is immaterial • the n-tuples are distinct • An n-tuple is an ordered list of values: • The ith value is in the ith domain • The ordering of domains is significant

5. Relations: Properties • Values of each column are homogeneous(belong to the same domain) • Rows are different with respect to one another R is a set of tuples (not a bag) • The order of columns is irrelevant, they are identified by name, not by position • The order of rows is irrelevant, they are identified by content, not by position

6. Relations: Definitions and Properties • Some loose definitions: • “attribute” corresponds to a named table column; • “tuple” corresponds to a table row; • “values” correspond to table cells • each attribute has a domain set from which its values come from

7. Relation: Example Attributes BUILDING Build.num Street name Nb floors Value 412 415 287 288 Laurier Laurier Ste-Foy Ste-Foy 2 3 3 2 142000 167000 256000 195000 Tuple Values

8. Schemas and Instances • Relation schema: A relation name and a set of distinct attribute names: R(A1 A2 ….An) Database schema: A set of relation schema with distinct relation names: R • Relation instance defined over schema R (X): finite set r of tuples over XDatabase instance defined over schema R:R = (R1 (X1), R2 (X2), ….Rn(Xn) ) = a set of relations (r1, r2, …., rn)

9. Constraints • What are they ? • represent the semantics of the miniworld being modeled. • restrict the set of possible database states. • Why do we want to specify the constraints? • Useful information to application programmers. They can write programs to prevent constraints violation: • example: acct balance should not fall below 0 £ • DBMS mightenforcespecified constraints directly making task of application writer easier !

10. Constraints in RDBMS • Domain constraints • Primary keys • Foreign keys • Entity integrity • Referential integrity • Semantic integrity

11. Constraints • Principles: • Defined for a database schema • Must hold for every database instance • Domain Integrity: • All values for an attribute must be a value from the attribute’s domain • Must be a SINGLETON (atomic) value(First-normal form)

12. Primary Keys • A key is a set of columns that together makes each row distinct • No two rows will have the same value of this key (implies that no two rows are allowed to be identical) • A primary key is usually included as a single column (e.g., ID) • Every relation has a key

13. Primary keys: Example • ModuleInfo(C#, semester, room, time, instructor) • Each module: one semester and one instructor • key = {C#, semester} • Each course: one semester and > 1 instructor • key = {C#, semester, instructor}

14. Primary keys • Aplication : • DBMS can check if the the tuple being inserted matches an existing tuple on the attributes in the key and disallow such insertions • Similarly, a primary key should not contain a null value since then it may not be possible to identify some tuples

15. Foreign Keys • Foreign keys refer to primary keys in other tables (e.g., Phys) • Foreign keys allow to represent relationships in RDBMS • A referential integrity constraint from the attribute Phys of Patient to Physicians means that Phys is a foreign key that refers to the primary key of Physicians

16. Physicians ID Name Phone Foreign Keys in Relations: Example Patient Pt Enc_Date Num Phys

17. Referential integrity • If we keep a list of existing Physicians - Physicians.ID = {1, 2, 3}, and have a referential integrity constraint saying that every value of Patient.Phys must also be a value of Physicians.Id then we can reject the following request: • insert into Patient values (“15”, “Smith”, “1-01-98”, “15”);

18. Entity & Referential Integrity • Entity Integrity: • Must be able to distinguish two entities by their primary key • No primary key value may be NULL • No restriction on non-primary key values • Referential Integrity: • A tuple containing a non-NULL foreign key must refer to an existing tuple in the foreign relation

19. ID Name Age 10932664 10932561 10932561 Dick Jane Spot 140 41 36 Semantic Integrity • Not all finite set of tuples are acceptable : • even if tuples have the right degree and attribute values:

20. Data Definition Language (DDL) • Used to encode database structure, including: • Table Definition (Name, Field Names, Domains,Keys) • Indexes/Physical Keys • Constraints needed to ensure referential integrity, validity, etc.

21. Data Manipulation Language (DML) • Used to add/delete/modify records in table instances • Categories: • Relational algebra and Relational Calculus • Transform-oriented language (e.g., SQL) • Query-By-Example (QBE) • User Views/Interfaces: • Forms • Application programs • Query/update language commands

22. Levels of Abstraction & Optimization • Relational languages and DBMS are highly “non-procedural” • Query describes table needed • DBMS figures out how to produce it • … but some queries are better than others • ambiguities • speed • computer system resources used

23. Relational DBMS: People involved • DBMS designers and implementers • Tool designers • Database administrator (DBA) I.e. ‘super-user’ for a database, similar to a system administrator. DBA can define schemas, views, authorization, indexes, tuning parameters, etc. • Application programmers • Database designers (interact with users to define database at all levels) • Operators and maintenance • End users

24. Relational DBMS: Technology Trends • Popularity of network of workstations (client-server database technology) • Expanding Internet (information filtering over the net, digital library, security and protection over the net, new transaction management issues) • Reducing cost of secondary storage. • large databases (many terrabytes, may be petabytes) of digital images, video and other complex data -- satellite images, medical images, etc. • data mining -- searching for useful knowledge in large volumes of data.

25. Relational DBMS: Application Pull • New applications -- Computer Aided Design and Manufacturing, Image and graphic databases, geometric databases. • Relational model not very good for modeling data in these domains • Led to emergence of Object oriented database management systems. that are designed to meet the requirements of these complex domains. • Key features: • flexibility to meet the requirements of these domains without being limited by data type and query language. • power to designer to specify both structure of complex objects and their behavior (operations)

26. Relational concepts • Review: • What’s a RDBMS ? • What’s a relation ? • How are they represented in a DBMS? • Where do these relations come from?

27. Discussion: Is MS Excel a RDBMS? • Is a spreadsheet a database? • Does MS Excel do all the functions of a database? How? • Data definition • Data manipulation • Data security & integrity • Data recovery and concurrency • Data dictionary • Query optimization

28. RDBMS Product Examples • DBase: • A standard for PC DBMS products, includes • dBase language (std) • browser • application generator • SQL • and other tools • Paradox: • Paradox language • usual tools • Front end for SQL db areas

29. RDBMS Product Examples • Gupta SQLBase with SQL Windows : • new kids on block (startup from other companies) • good early reviews • 1st client-server on PCs • clean gateway to IBM’s DB2 • supports blobs, cursors • good development environment

30. RDBMS Product Examples • Microsoft SQL Server : • fastest PC DBMS • usual suite of tools • extension of dBase language • Access : • newest offering • Visual Basic front end to SQL and usual tools • Can read files from other PC DBMS vendors • hot seller

31. RDBMS Product Examples • Ingres : • Descendant of Berkeley project (Postgres) • still the most technically polished products, innovative (e.g. in distributed DBMSs, rules) • Good tools for DBA, application creation • Oracle and Sybase : • Fairly technically advanced (early with client/server, triggers) • Major commercial SQL product

32. Review Questions • What is meant by relational database structure? • What are the minimal constraints ? Why do we need them? • What’s a relation again? • How are they represented in a DBMS?

33. Exercises CUSTOMER SALES_ORDER Cust-no Order-no Name Address Date Cust-no Smith Smith 1/7/97 C45 C45 11 Baker Paul 15/7/97 C46 C46 12 Foreign key Primary key (1) For each of the following tuples, states which, if any, of the integrity rules are violated when they are added to the SALES_ORDER relation: <14, 1/6/97, C45> <15, 1/7/97, null> <16, 5/7/97, C52> <17, 10/7/98, null>