Database 15a2u

1&2 The DBMS Manages the Interaction Between the End and the Database

1

1&2 The Database System Environment (continued)

2

1&2

DBMS Functions 1. Data dictionary management • •

defines data elements (data structure) and their relationships (meta data) provide data abstraction,remove structural + data dependency

2. Data storage management •

stores data and related data entry forms, data validation, report definitions, etc.

3. Data transformation and presentation •

translates logical requests into commands to physically locate and retrieve the requested data (data independence)

3

1&2

DBMS Functions (continued) 4. Security management •

enforces security and data privacy (who can access to which data + what operations they can perform)

5. Multi- access control •

creates structures that allow multiple s to access the data

6. Backup and recovery management •

provides backup and data recovery procedures

4

1&2

DBMS Functions (continued) 7. Data integrity management •

promotes and enforces integrity rules to eliminate data integrity problems (minimize redundancy, maximize consistency)

8. Database communication interfaces •

allows database to accept end- requests within a computer network environment

•

accept request through network, web browser

5

1&2

DBMS Functions (continued) 9. Database access languages and application programming interfaces •

query language  nonprocedural language –

Data Definition Language (DDL) define structure in which data are stored

–

Data Manipulation Language (DML) allow to extract data from database

6

Illustrating Metadata with Microsoft Access

1&2

7

Illustrating Data Storage Management with Oracle

1&2

8

1&2

Types of Databases classified by # of s: 1. Single-  s only one at a time •

Desktop  single- database running on a personal computer

2. Multi-  s multiple s at the same time •

Workgroup  s a small group of s or a single department

•

Enterprise  s a large group of s or an entire organization

9

1&2 Types of Databases classified by location of DB: 1. Centralized: –

s data located at a single site

2. Distributed: –

s data distributed across several sites

10

1&2 Types of Databases classified by uses of DB: 1. Transactional / Production – s a company’s day-to-day operations – time-critical, accurate, immediate recording of data – emphasize on : data integrity, consistency, operational speed 2. Data warehouse / DSS: – stores data used to generate information required to make tactical or strategic decisions (middle to high – extract historical data (time factor not critical), and aggregated data from many sources – eg)) Use to make pricing decision, sale forecast, market positioning 11

1&2

Database vs. File System • File system – Many separate and unrelated files

• Database – Logically related data stored in a single logical data repository – eliminate data inconsistency, data anomalies

12

1&2

Contrasting Database and File Systems

13

1&2

Database Design and Modeling • Model  abstraction of real world events or objects • Data model  collection of logical constructs used to represent the data structure, characteristics, relations, constraints and transformations found in the database • help in easier database design • good model  good DB design  good application

14

1&2

Data Model Basic Building Blocks • Entity – Anything (person, thing, place, event) about which data are to be collected and stored – Customer, agent, order

• Attribute – Characteristic of an entity – Customer first name, phone, address

• Relationship – Association among two or more entities – An agent can serve many customers, a customer may be served by one agent. – One-to-Many (1:M) – Many-to-Many (M:N) – One-to-One (1:1) 15

1&2

Business Rules • Brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization’s environment • Apply to any organization that stores and uses data to generate information

• Description of operations that help to create and enforce actions within that organization’s environment • Define entities, attributes, relationships and constraints 16

1&2

Business Rules (continued) • Must be rendered in writing

• Must be kept up to date • Must be easy to understand and widely disseminated

• Describe characteristics of the data as viewed by the company

17

1&2

Importance of Business Rules

• Standardize company’s view of data • Constitute a communications tool between s and designers • Allow designer to understand the nature, role, and scope of data • Allow designer to understand business processes • Allow designer to develop appropriate relationship participation rules and constraints

18

1&2

Sources of Business Rules • Company managers

• Policy makers • Department managers • Written documentation – Procedures – Standards – Operations manuals

• Direct interviews with end s

19

1&2 Business Rules for Video Rental shop Sample • • • • • •

A customer may or may not as a member Only member can get discount A movie have many copies A rental can contain maximum of 3 movies copies A movie copies can be rented in many rental A rental must be returned within 3 days

20

1&2

Evolution of Data Models: • • • • •

Hierarchical Model Network Model Relational Model Entity-Relationship Model Object-Oriented Model

21

1&2

The Hierarchical Model—Evolution • GUAM (Generalized Update Access Method) – Based on the recognition that the many smaller parts would come together as components of still larger components

• Best understood by examining manufacturing process • Best for 1:M relationship that remain fixed over time (same part, same way)

22

1&2

A Hierarchical Model’s Logical Structure

23

1&2 Hierarchical Database Structure—Characteristics • • • •

hierarchy of segments (= records) Each parent can have many children 1:M Each child has only one parent Hierarchical path – Preorder traversal or hierarchic sequence (Left-list path) • start from root • For efficient data access, most frequently accessed segment closest to left

24

1&2

Network Database Model—Basic Structure • Resembles hierarchical model (collection of records in 1:M relationships) • unlike as it also M:N relationship • Set/Relationship (explicit) – Composed of at least two record types • Owner = hierarchical model’s parent • Member = hierarchical model’s child (can have > 1 owners)

25

1&2

A Network Data Model

26

1&2 The Relational Database Model—Basic Structure • implemented through Relational Database Management System (RDBMS) • Performs same basic functions provided by hierarchical and network DBMS systems ++

• advantage : able to let the /designer operate in a human logical environment • RDBMS will manage the physical detail

27

The Relational Database Model— Basic Structure (continued)

1&2

• Table (relations) – Matrix consisting of a series of row/column intersections – Related to each other by sharing a common entity characteristic (eg. CUSTOMER table relates to AGENT table by AGENT_CODE)

• Relational schema – Visual representation of relational database’s entities, attributes of entities, and relationships between entities

28

1&2

A Relational Schema

29

1&2

Linking Relational Tables

30

1&2

The Relational Database Model • Advantages – Structural independence  change db structure without the need to change application – Improved conceptual simplicity focus on how human see data (ignore physical data storage characteristics) – Easier database design, implementation, management, and use – Ad hoc query capability  SQL (4GL) – Powerful database management system  hide system complexity (physical structure) from designer and end

31

1&2

The Relational Model (continued) • Disadvantages – Substantial hardware and system software overhead

– Easy-to-use • poor design and implementation (slow system + data anomalies)

• promote “islands of information” problems (each department create their own database)

32

1&2

The Entity Relationship Data Model • Graphical representation of entities and their relationships in a database structure • Introduced by Chen in 1976 • Crow’s Foot Model

33

The Entity Relationship Model— Basic Structure

1&2

• Entity relationship diagram (ERD)

• • • •

– Uses graphic representations to model database components – Entity is mapped to a relational table Entity set/ Entity = collection of like entities Entity instance / occurrence = row in table Attribute = characteristics of entity Relationship = association among data (diamond shape) – Connectivity  labels types of relationships • One-to-Many (1:M) • Many-to-Many (M:N) • One-to-One (1:1)

34

1&2

Relationships: The Basic Chen ERD

35

Relationships: The Basic Crow’s Foot ERD

1&2

36

The Entity Relationship Model— Basic Structure

1&2

• Advantage – conceptual view – visual representation – effective communication tools among (database designer, programmers, managers, end s)

• Disadvantage – limited constraints representation (eg. GPA between 0 and 4.0) – limited relationship representation  only between entities, not within entity (eg. classification and credit completed) – loss of information content  crowed model so ignore attribute mapping

37

1&2

The Object Oriented Model • collection of objects (object = data+ relationships) • Now ,object  attributes + relationship + behaviors unlike ERD Model (entity set) – attribute  characteristic (name, ssc#, dob) – Behaviors/Method  real-world action (find PERSON’s name, print address) • Class  group objects that share similar characteristics and behavior EG. PERSON • Classes are organized in Class hierarchy • 1 class : 1 parent (PERSON:CUSTOMER),(PERSON,EMPLOYEE) • Inheritance  ability of an object within the class hierarchy to inherit the attributes and methods of classes above it 38

A Comparison of the OO Model and the ER Model

1&2

39

1&2

The Development of Data Models

40

1&2

The SQL environment • Following Fig. Shows a schematic of an SQL environment • Each database is contained in a catalog, which describes any object that is part of the database (regardless of which created that object). Most companies keep at least two versions of any database they are using. PROD_C is the live production version, which captures real business data and this must be very tightly controlled and monitored. DEV_C is the development version used when the database is being built and continues to serve as a development tool where enhancements and maintenance efforts can be tested before application to the production database. • Each database will have a set of schemas associated 41 with a catalog.

1&2 A simplified

schematic of a typical SQL

environment

42

1&2

3 types of SQL commands

• 1. Data Definition Language (DDL) commands - that define a database, including creating, altering, and dropping tables and establishing constraints • 2. Data Manipulation Language (DML) commands that maintain and query a database • 3. Data Control Language (DCL) commands - that control a database, including istering privileges and committing data

43

1&2 DDL,

DML, DCL, and the database development pr

44

SQL Data types

1&2

• CHAR(n) – fixed-length character data, n characters long Maximum length = 2000 bytes

• VARCHAR2(n) – variable length character data, •

• • • •

maximum 4000 bytes LONG – variable-length character data, up to 4GB. Maximum 1 per table NUMBER(p,q) – general purpose numeric data type INTEGER(p) – signed integer, p digits wide FLOAT(p) – floating point in scientific notation with p binary digits precision DATE – fixed-length date/time in dd-mm-yy form

45

1&2

Syntax used in these notes • Capitals = command syntax (may not be required by the RDBMS) • Lowercase = values that must be supplied by • Brackets = enclose optional syntax • Ellipses (...) = indicate that the accompanying syntactic clause may be repeated as necessary • Each SQL command ends with a semicolon ‘;’ • In interactive mode, when the presses the RETURN key, the SQL command will execute

46

1&2

SQL Database Definition • Data Definition Language (DDL) has these major CREATE statements: – CREATE SCHEMA – defines a portion of the database owned by a particular . Schemas are dependent on a catalog and contain schema objects, including base tables and views, domains, constraints, assertions, character sets, collations etc. – CREATE TABLE – defines a new table and its columns. The table may be a base table or a derived table. Tables are dependent on a schema. Derived tables are created by executing a query that uses one or more tables or views. 47

1&2

SQL Database Definition • CREATE VIEW – defines a logical table from one or more tables or views. There are limitations on updating data through a view. Where views can be updated, those changes can be transferred to the underlying base tables originally referenced to create the view.

48

1&2

SQL database definition • Each of the previous create commands may be reversed using a DROP command, so DROP TABLE will destroy a table (including its definitions, contents and any schemas or views associated with it) • Usually only the table creator may delete the table • DROP SCHEMA and DROP VIEW will also delete the named schema or view. • ALTER TABLE may be used to change the definition of an existing table

49

1&2

Creating tables • Once data model is designed and normalised, the columns needed for each table can be defined using the CREATE TABLE command. The syntax for this is shown in the following Fig. These are the seven steps to follow: • 1. Identify the appropriate datatype for each , including length and precision • 2. Identify those columns that should accept null values. Column controls that indicate a column cannot be null are established when a table is created and are enforced for every update of the table • 50

1&2

Creating tables • 3. Identify those columns that need to be UNQUE when the data in that column must have a different value (no duplicates) for each row of data within that table. Where a column or set of columns is designated as UNIQUE, this is a candidate key. Only one candidate key may be designated as a PRIMARY KEY • 4. Identify all primary key-foreign key mates. Foreign keys can be established immediately or later by altering the table. The parent table in such a parentchild relationship should be created first. The column constraint REFERENCES can be used to enforce referential integrity

51

1&2

Creating tables • 5. Determine values to be inserted into any columns for which a DEFAULT value is desired - can be used to define a value that is automatically inserted when no value is provided during data entry. • 6. Identify any columns for which domain specifications may be stated that are more constrained than those established by data type. Using CHECK it is possible to establish validation rules for values to be inserted into the database • 7. Create the table and any desired indexes using the CREATE TABLE and CREATE INDEX statements 52

Table creation General

1&2

syntax for CREATE TABLE

53

1&2

Table creation • The following Fig. Shows SQL database definition commands • Here some additional column constraints are shown, and primary and foreign keys are given names • For example, the CUSTOMER table’s primary key is CUSTOMER_ID • The primary key constraint is named CUSTOMER_PK, without the constraint name a system identifier would be assigned automatically and the identifier would be difficult to read

54



SQL database definition commands for Pine Valley Furniture

1&2

55

1&2

STEP 1

Defining

attributes and their data types

56

1&2

STEP2

Non-nullable

specifications

Note:

primary keys should not be null

57

1&2

STEP 3

Identifying

primary keys

This

is a composite primary key

58

1&2

STEP 4

Identifying

foreign keys and establishing relationships

59

STEPS

1&2

5 and 6

Default

values and domain constraints

60

1&2

STEP 7

Overall

table definitions

61