Distributed Database Design

1
Distributed Database Design

• COSC 5040
• Week One

2
Outline

• Introduction
• Course overview
• Database systems concepts
• Relational database model
• Structured query language (SQL)

3
Database System Concept

• Data
• Known facts
• Database
• A collection of related data
• Database Management System (DBMS)
• A software system to facilitate the defining,
  constructing, manipulating, and sharing of a
  computerized database
• Database System
• The DBMS software together with the data itself
• Sometimes, the applications are also included

4
Typical DBMS Functionality

• Define a database
• Construct and load the database
• Manipulating the database
• Querying, generating reports, insertions,
  deletions and modifications
• Concurrent processing and sharing
• Protection or security
• Presentation and visualization

5
Database System Environment
6
Example of a Database

• Figure 1.2 A database that stores student and
  course information.

7
Example of a Database
8
Database Manipulation

• Database manipulation involves querying and
  updating
• P. 9
• Examples of queries
• Examples of updates

9
Database Approach Characteristics

• Self-describing nature of a database system
• Meta-data
• Insulation between programs and data, data
  abstraction
• Program-data independence
• Support of multiple views of the data
• Virtual data
• Sharing of data and multi-user transaction
  processing
• Concurrency control

10
Database Users

• Actors on the scene
• Database administrators (DBA)
• Authorizing access to the database
• Acquiring software, and hardware resources
• Controlling and monitoring efficiency of
  operations
• Database designers
• Define content, structure, constraints, and
  functions or transactions
• Communicate with the end-users
• End-users
• Queries, reports
• Update the database content
• Actors behind the scene

11
Database Users
12
Advantages of Database Approach

• Controlling redundancy
• Restricting unauthorized access
• Providing persistent storage
• Providing storage structures for efficient query
  processing
• Providing backup and recovery
• Providing multiple interfaces
• Representing complex relationships among data
• Enforcing integrity constraints
• Drawing inferences and actions

13
Historical Development

• Early database applications
• Hierarchical model
• Network model
• Relational model based systems
• Object-oriented applications OODBs and ORDBMSs
• Web and e-commerce applications
• Database for new applications

14
Data Models

• Data model
• Data abstraction
• A collection of concepts that can be used to
  describe the structure of a database
• Entities, attributes, relationships
• Data types, constraints
• Categories of data models
• Conceptual (high-level, semantic) data models
• Implementation (representational) data models
• Physical (low-level, internal) data models

15
Schemas and Instances

• Database schema
• Description of a database
• Schema diagram
• Diagrammatic display of a database schema
• Database state
• Actual data in the database at a particular
  moment in time
• Current set of occurrences or instances

16
Schema Diagram
17
Three-Schema Architecture
18
Data Independence

• Logical data independence
• The capacity to change the conceptual schema
  without having to change the external schemas and
  their application programs
• Physical data independence
• The capacity to change the internal schema
  without having to change the conceptual schema

19
DBMS Languages

• Structured query language (SQL)
• Data definition language (DDL)
• To specify database conceptual schema
• Data manipulation language (DML)
• To specify database retrievals and updates
• DBMS Interfaces
• Stand-alone query language interfaces
• Programmer interfaces for embedding DML in
  programming languages

20
Database System Utilities

• To perform certain functions such as
• Loading data stored in files into a database
• Data conversion tools
• Backing up the database periodically
• Reorganizing database file structures
• Report generation utilities
• Performance monitoring utilities
• Sorting, user monitoring, data compression
• Data dictionary

21
Client-Server Architectures

• Centralized architecture
• Client-server architecture
• Client
• Provide appropriate interfaces and a
  client-version of the system to access and
  utilize the server resources
• Server
• Provides services to clients
• Database server provides database query and
  transaction services to clients

22
Three Tier Client-Server Architecture
23
Classification of DBMS

• Based on data model
• Relational
• Network
• Hierarchical
• Object-oriented
• Object-relational
• Other classifications
• Single-user vs. multi-user
• Centralized vs. distributed

24
Relational Model Concepts

• The relational model is based on the concept of a
  relation
• A relation is a mathematical concept based on the
  ideas of sets
• Relation A table of values
• Contains a set of rows and columns

25
Example of a Relation
26
Definitions

• The schema, or description of a relation
• R (A1, A2, .....An)
• CUSTOMER (Cust-id, Cust-name, Address, Phone)
• A tuple is an ordered set of values
• Each value is derived from an appropriate domain
• A domain is a set of atomic values
• Data type or format
• An attribute designates the role played by the
  domain

27
Definitions

• The relation is formed over a subset of the
  Cartesian product of the sets
• Each set has values from a domain
• That domain is used in a specific role which is
  the attribute name
• Given R(A1, A2, .........., An)
• r(R) ? dom (A1) X dom (A2) X ....X dom(An)
• R schema of the relation
• r of R a specific "value" or population of R

28
Example

• Let R(A1, A2)
• Let dom(A1) 0,1
• Let dom(A2) a,b,c
• Then, for example
• r(R) lt0,agt , lt0,bgt , lt1,cgt
• is one possible state or population or
  extension r of the relation R, defined over
  domains D1 and D2
• It has three tuples

29
Definition Comparison
Informal Terms Formal Terms
Table Relation
Column Attribute
Row Tuple
Values in a column Domain
Table Definition Schema of a Relation
Populated Table State of the Relation
30
Characteristics of Relations

• Ordering of tuples in a relation r(R)
• The tuples are not considered to be ordered
• Ordering of values within each tuple
• The attributes in R(A1, A2, ..., An) and the
  values in tltv1, v2, ..., vngt are ordered
• Values in a tuple
• All values are considered atomic (indivisible)
• A special null value is used to represent values
  that are unknown or inapplicable to certain tuples

31
Relational Integrity Constraints

• Constraints are conditions that must hold on all
  valid relation instances
• Types of constraints
• Domain constraints
• Key constraints
• Entity integrity constraints
• Referential integrity constraints

32
Key Constraints

• Uniqueness
• A set of attributes of R such that no two tuples
  in any valid relation instance r(R) will have the
  same value
• Minimal
• Removal of any attribute results in a set of
  attributes that is not a key
• If a relation has several candidate keys, one is
  chosen to be the primary key
• The primary key value is used to uniquely
  identify each tuple in a relation

33
Foreign Key

• A set of attributes in one relation that
  references the primary key in another relation
• Same domain(s)
• Value of foreign key either occurs as a value of
  primary key or is null

34
Entity and Referential Integrity

• Entity integrity constraint
• No primary key value can be null
• Referential integrity constraint
• Foreign key value can be either an existing
  primary key value or a null value

35
Update Operations

• Update operations
• Insert a tuple (p. 76)
• Delete a tuple (p. 77)
• Update a tuple (p. 78)
• Maintain integrity constraints
• Child insert restrict
• Child update restrict
• Parent update restrict
• Parent delete restrict

36
Relational Database Schema
37
(No Transcript)
38
Exercise 3.16
Consider the following relations for a database
that keeps track of student enrollment in courses
and the books adopted for each course STUDENT(SS
N, Name, Major, Bdate) COURSE(Course, Cname,
Dept) ENROLL(SSN, Course, Quarter,
Grade) BOOK_ADOPTION(Course, Quarter,
Book_ISBN) TEXT(Book_ISBN, Book_Title, Publisher,
Author) Specify the foreign keys for this
schema, stating any assumptions you make.
39
SQL

• Structured query language (SQL)
• SQL-86 or SQL1
• SQL-92 or SQL2
• SQL-99 or SQL3
• Comprehensive database language
• Data definition (DDL)
• Data manipulation (DML)
• Query
• Update

40
Data Definition Language (DDL)

• Used to CREATE, DROP, and ALTER the descriptions
  of the tables (relations) of a database
• Data types
• Numeric
• Character string
• Boolean
• Data/time

41
CREATE TABLE

• Specifies its name, its attributes and their data
  types
• A constraint NOT NULL may be specifiedCREATE
  TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT
  NULL, DNUMBER INTEGER NOT NULL, MGRSSN
  CHAR(9), MGRSTARTDATE CHAR(9))

42
CREATE TABLE

• Use the CREATE TABLE command for specifying
• Primary key attributes
• Secondary keys, and
• Referential integrity constraints (foreign keys)
• CREATE TABLE DEPT
• ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER
  NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE
  CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE
  (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP )

43
DROP TABLE and ALTER TABLE

• Remove a relation (base table) and its definition
• DROP TABLE DEPENDENT
• Add an attribute to one of the base relations
• ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12)

44
Retrieval Queries in SQL

• One basic statement for retrieving information
  from a database
• SELECT statement
• Basic form is a SELECT-FROM-WHERE block
• SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt

45
Simple SQL Queries

• Query 0
• Retrieve the birthdate and address of the
  employee whose name is 'John B. Smith'
• SELECT BDATE, ADDRESS
• FROM EMPLOYEE
• WHERE FNAME'John'
• AND MINIT'B'
• AND LNAME'Smith'

46
Simple SQL Queries

• Query 1
• Retrieve the name and address of all employees
  who work for the 'Research' department
• SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE,
  DEPARTMENT WHERE DNAME'Research'
• AND DNUMBERDNO
• DNAME'Research' is a selection condition
• DNUMBERDNO is a join condition

47
Simple SQL Queries

• Query 2
• For every project located in 'Stafford', list the
  project number, the controlling department
  number, and the department manager's last name,
  address, and birthdate
• SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
  FROM PROJECT, DEPARTMENT, EMPLOYEEWHERE
  DNUMDNUMBER AND MGRSSNSSN
• AND PLOCATION'Stafford'
• There are two join conditions
• DNUMDNUMBER relates a project to its controlling
  department
• MGRSSNSSN relates the controlling department to
  the employee who manages that department

48
Aliases

• A query that refers to the same name must qualify
  the attribute name with the relation name
• Some queries need to refer to the same relation
  twice
• Query 8
• For each employee, retrieve the employee's name,
  and the name of his or her immediate
  supervisorSELECT E.FNAME, E.LNAME, S.FNAME,
  S.LNAMEFROM EMPLOYEE E, EMPLOYEE
  SWHERE E.SUPERSSNS.SSN

49
Unspecified Where-Clause

• Query 9
• Retrieve the SSN values for all employees
• SELECT SSNFROM EMPLOYEE
• Query 10
• Retrieve the SSN and department name values for
  all employees
• SELECT SSN, DNAMEFROM EMPLOYEE, DEPARTMENT
• Resulting CARTESIAN PRODUCT

50
Use of Asterisk

• Q1C
• SELECT FROM EMPLOYEE WHERE DNO5
• Q1D
• SELECT FROM EMPLOYEE, DEPARTMENT WHERE DNA
  ME'Research' AND DNODNUMBER
• To retrieve all the attribute values

51
Use Of Distinct

• To eliminate duplicate tuples in a query result,
  the keyword DISTINCT is used
• Q11
• SELECT SALARYFROM EMPLOYEE
• Q11A
• SELECT DISTINCT SALARYFROM EMPLOYEE

52
Set Operations

• UNION, MINUS and INTERSECT operations
• Query 4
• Make a list of all project numbers for projects
  that involve an employee whose last name is
  'Smith' as a worker or as a manager of the
  department that controls the project
• (SELECT PNAME FROM PROJECT, DEPARTMENT,
  EMPLOYEE WHERE DNUMDNUMBER AND MGRSSNSSN
• AND LNAME'Smith') UNION
• (SELECT PNAME FROM PROJECT, WORKS_ON,
  EMPLOYEE WHERE PNUMBERPNO AND ESSNSSN
• AND LNAME'Smith')

53
Substring Matching

• Query 12
• Retrieve all employees whose address is in
  Houston, Texas
• SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE ADDRESS LIKE 'Houston, TX

54
Arithmetic Operations

• Query 13
• Show the resulting salaries if every employee on
  the ProductX project is given a 10 percent
  raise
• SELECT FNAME, LNAME, 1.1SALARY AS INCREASED_SAL
• FROM EMPLOYEE, WORKS_ON, PROJECT
• WHERE SSNESSN AND PNOPNUMBER AND
  PNAMEProductX

55
Ordering of Query Results

• Query 15
• Retrieve a list of employees and the projects
  they are working on, ordered by department and,
  within each department, ordered alphabetically by
  last name, first name
• SELECT DNAME, LNAME, FNAME, PNAME
• FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
• WHERE DNUMBERDNO AND SSNESSN AND PNOPNUMBER
• ORDER BY DNAME, LNAME, FNAME

56
Specifying Updates in SQL

• There are three SQL commands to modify the
  database
• INSERT
• DELETE, and
• UPDATE

57
INSERT

• U1
• INSERT INTO EMPLOYEE
• VALUES ('Richard', 'K', 'Marini', '653298653',
  1962-12-30', '98 Oak Forest,Katy,TX', 'M',
  37000, '987654321', 4)
• U1A
• INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)
• VALUES ('Richard', 'Marini', '653298653')

58
DELETE

• U4
• DELETE FROM EMPLOYEE WHERE LNAME'Brown'
• DELETE FROM EMPLOYEE WHERE SSN'123456789'
• DELETE FROM EMPLOYEE WHERE DNO5
• DELETE FROM EMPLOYEE

59
UPDATE

• U5
• Change the location and controlling department
  number of project number 10 to 'Bellaire' and 5,
  respectivelyUPDATE PROJECTSET PLOCATION
  'Bellaire', DNUM 5WHERE PNUMBER10
• U6
• Give all employees in the 'Research' department a
  10 raise in salaryUPDATE EMPLOYEESET SALARY
  SALARY 1.1WHERE DNO IN 5

60
Reading and Homework

• Readings
• Chapter 1, 2, 3, and 4
• Week one homework