COMP 2005 Personal Database Tools Week 3'1

1
COMP 2005Personal Database Tools - Week 3.1
2
Database Development Stages

• Conceptual Model - Infological Model
• Logical Model - Datalogical Model
• Physical Design - Programming

3
Relational Database Systems
4
The Relational Database Model

• All data is modelled as simple tables of related
  information
• No duplicate rows
• Order of rows or columns is unimportant
• each entry has a simple value
• integer, string
• Matching attribute values indicate connections
• Derived from the principles of mathematical sets

5
A Simple Example
Employee
Duty
Two relations, sharing the common attribute ID
6
Terminology - 1

• Two standard terminologies are in use
• Table/Column/Row
• and Relation/Attribute/Tuple
• Relation one table with a set of attributes
• Employee(ID, Name, Salary)

7
Terminology - 2

• Attribute - name of a column
• describes the contents of the column
• each component is atomic elementary fact type
• cant be a list or array

8
Terminology - 3

• Tuple - a row of attribute values in a table
• has a component of each attribute of the relation
• one particular tuple appears only once in a table
• e.g.( Smith, 55, librarian, 24-8-52)
• formal notion of a tuple - function from
  anattribute to tuple value
• Person-gt Jon, DOB-gt23/8/47, Gender -gt M

9
Terminology - 4

• Domain the range of values an attribute can
  take on
• e.g. the set of values for cities
• Key a set of attributes in a relation, on which
  no two tuples can be identical
• e.g. the ID number for people
• the ID and course for an enrolement

10
Terminology- 5

• Schema the name of a table and columnse.g.
  Duty(ID, Project, Location)
• Database Schema the set of relational schemas
  for a database
• Instance the particular values in a database or
  relation at one instant of time - the dataany
  tuple change creates a new instance
• Current Instance - the Table at the moment

11
Theory vs. Practice

• Commercial implementations usually allow
  duplicate rows
• they force uniqueness by constraints or keys
• Commercial systems usually allow ordering of the
  rows

12
Limitations of the Relational Model

• What is not well represented in a relational
  model
• Pictures, maps, plans
• Procedural information
• Complex structures
• Active data

13
Data Abstraction - three layers

• Internal schema (physical, storage)
• describes layout of records in files
• describes length and format or record items
• Logical schema (community/organisational)
• describes the meaning of relations
• External schema (user) - VIEW
• each user may want to see (or be permitted to
  see) only a part of the total data

14
Data Independence

• The ability to change decisions at one level
  without effecting higher levels
• allows systems to evolve without rewriting all
  applications
• DBMS gives physical data independence as database
  access is through the DBMS
• Relational DBMS provide logical data
  independence, as users can be provided with their
  own view of the data

15
The DBMS Interface

• Data Definition Language (DDL)
• describes the schema
• names relations, attributes
• defines types of attributes
• defines constraints to be enforced by the
  software
• stored in the system catalogue (data dictionary)
  SYS.CAT

16
The DBMS Interface

• Data Manipulation Language (DML) - SQL
• commands to retrieve or modify data held in the
  database
• in relational systems, these are usually
  declarative, set oriented
• can usually be embedded into a general purpose
  programming language (C, C, COBOL, etc)

17
DBMS Structure

• Query Processor
• parses commands expressed in DML
• uses the system catalogue to convert this command
  to access of specific records
• where there is more than one way to find an
  answer, there is a need for optimisation
• this process is compiled for regular queries

18
DBMS Structure

• Access Methods
• uses physical data structure to fetch required
  records
• may use OS file system or raw disk

19
(No Transcript)
20
COMP 2005Personal Database Tools - Week 3.2
21
Introduction to SQL

• brief history
• simple queries using SQL
• advanced queries using aggregation and grouping
• data definition
• data modification

22
Brief History of SQL

• 1970 E. Codd proposes the relational model
• early 70s Research projects demonstrate
  relational implementations
• System R from IBM (language SEQUEL, later SQL)
• Ingres from University of California, Berkeley
  (language QUEL)

23
Brief History of SQL (contd.)

• early 80s Commercial products available
• DB2 and SQL/DS (IBM), Rdb (DEC), Informix,
  Ingres, ORACLE, SYBASE
• all used variants of SQL
• 1982 standards committee formed
• 1986 first ANSI standard
• 1992 SQL-92 is the most recent standard
• next? SQL3

24
Interactive SQL

• Each SQL statement uses the current value of the
  database to
• compute a relation that is returned to the user
• compute a relation that is stored
• modify a relation
• The result may have one or more columns and zero
  or more rows.
• SQL is declarative.
• it describes what is wanted and leaves the system
  to determine how to do it

25
Single Table Queries

• select select-listfrom tablewhere
  search-condition
• select-list is a comma separated list of
  table.column
• search-condition is a Boolean expression
  involving constants and table.column
• select sub-clauses - nested select clauses

26
Example

• Part (PartNo, Description, Weight, Colour,
  Instock)select Part.PartNo, Part.Instockfrom
  Partwhere Part.Weight gt 50
• Find the part number and quantity in stock of
  each part weighing more than 50kg.

27
Semantics/Computation

• Examine each tuple in named relation
• Evaluate search-condition on it
• If search-condition is true, then
• evaluate select-list
• place it in output relation
• Note the system does not have to compute the
  result this way, but the answer must be as if it
  did.

28
Multi Table Queries - 1

• Consider the following database schema
• Part (PartNo, Description, Weight, Colour,
  Instock)
• Supplier (SupplierId, Name, Address, Contact)
• Availability (PartNo, SupplierId, Price, Maxqty)

29
Multi Table Queries - 2

• Find the identifier and name of suppliers from
  who we can get parts weighing more than 50kg.
• select Supplier.SupplierId, Supplier.Namefrom
  Part, Supplier, Availabilitywhere Supplier.Suppl
  ierId Availability.SupplierIdand
• Availability.PartNo Part.PartNoand
• Part.Weight gt 50

30
Semantics/Computation

• Form all combinations of tuples, one from each
  named relation
• Evaluate search-condition on each one combination
• Select as for single table queries
• Warning a common mistake is to forget to equate
  the values of attributes between relations (join
  clause)

31
Syntactic Shortcuts

• Use col instead of table.col if unambiguous
• Use table. instead of listing all columns of
  table in select-list
• Use instead of listing all columns of all
  tables in select-list
• Omit where clause if search-condition is always
  true

32
Aliases

• Find pairs of suppliers with the same address
• select S1.Name, S2.Name
• from Supplier S1, Supplier S2
• where S1.Address S2.Address
• and S1.SupplierId ltgt S2.SupplierId
• Works on multiple copies of the same table
• Consider combinations in which aliases contribute
  different tuples and the same tuple

33
Extra Features

• Expressions in select-list
• select SupplierId, PartNo,
• Price Maxqty
• from Availability
• select distinct removes duplicate rows from the
  output
• order by outputcolumn (DESC) specifies the order
  of the rows
• LIKE p matches partial strings