Database Systems Chapter 6

CS 4210 Module 6- Tranforming Data Models into Database Designs

client/server model

the standard model of networked traffic in today’s database designs

peer to peer model

devices communicated one to one, used before c/s model. servers couldn’t handle multiple calls at once.

Multi-tier architecture

separation and duplication of server/DBMS by decentralizing data and computing resources. improves reliability and throughput

Six layers of Multi-Tier Architecture systems

Persistence: DB which server files to the applications that service the clients or users
Accessor: the SQL server that does the thinking
Logic: Applications the user interacts with, providing instructions to the DB from which files are requested
Presentation: Applications that package data from DB to web browser languages (HTML/XML)
Requester/Consumer: client side web browser
Elsewhere: sources of info held on other platforms (AWS, Azure, etc)

Three main components of Client/Server Systems

Presentation: controls the inputs and outputs and makes data readable
Processing Logic: oversees turning a client’s request into a request the server can use
Storage Logic: physical data storage and processes it for retrieving on user request

Different Client/Server distributions

Thin Client- Client only handles presentation, Server handles storage and processing

Large Client- Client handles presentation and processing, Server handles Storage

Distributed Client- Client and server both share Processing.

which Client Server distribution is the most common

Large Client

Two-tier Architecture

Client communicates with server directly. Good for small workgroups, not large applications. Client covers UI Logic and DB accesses and processes requests from clients

Three (N-tier) Architecture

Includes another server layer. Makes it easier to scale the system. Extra layer can be an application server or hold local storage or databases. Reduces dependency and has better performance. Developers can work concurrently.

A ___ is transformed into a ____

Data Model; Database Design

Database Design

Set of Database specifications to be implemented as a DB in a DBMS. Data Model is non-DBMS specific, but Database Design is DBMS-specific

Three Database Design Stages

Conceptual, Logical, Physical

Steps for transforming a Data Model into a Database Design

1) Create table for each entity
- Specify PK (consider Surrogate keys), specify alternate keys, specify properties for each column, make sure it’s normalized

2) Created relationships by placing FKs

-relationships btwn strong entities (1:1,1:N,N:M), relationships w ID-Dependent entities, relationships with strong and weak (not ID), mixed relations, supertype/subtype relations, and recursive relations

3)Specify Logic for enforcing Minimum cardinality

(O-O,M-O,O-M,M-M)

Ideal primary key is

short, fixed, and numeric

Disadvantage of Surrogate keys

They are meaningless to users, when databases are shared multiple things might have the same surrogate key

Representing Alternate keys

AKn.m : n is the number of the alternate key, m is the column number in that alternate key.

Generic data types

char(n), Nchar(n), varchar(n), date, time, Integer, Decimal(m,n), Numeric(m,n), Money(m,n)

Domain constraints

Limits column values

Range constraints

Limits values of a row to particular intervals

Intrarelation Constraints

limits a columns value in comparison of other columns in the same table

Interrelation Constraints

Limits a columns values in comparison to other columns in other tables (the ref integrity constraint is a interrelation constraint)

How to implement a N:M relationship between strong entities

No place for FK in either table, so you create an intersection table, consisting of the primary keys of both tables, and each one is a PK and FK.

Uses for ID-Dependent Entities

rep N:M relationships, rep association relationships, rep multivalued attributes, rep archetype/instance relationships

An association table has

-all characteristics of intersection tables

-plus one or more columns of attributes specific to the associations of the other two entities

MUST Constraint (representing Ternary and higher order relationships)

requires that one entity must be combined with another entity in the ternary relationship ( if 20 → 2000 in one table, then another table with the second column has a 2000 entry, 20 must also be there)

MUST NOT constraint

requires certain combinations of entities arent allowed to occur (if someone is allergic to peanuts their entity and peanut entity can both exist)

MUST COVER constraint

a binary relationship specifies all combinations of two entities that must appear

cascading updates

when a change to a parents primary key is applied to the child’s foreign key

cascading deletes

when associated child rows are deleted along w/ the deletion of parent row

-generally, you don’t want to cascade delete strong entities, but you do with weak entities

triggers

module of code invoked by DBMS when specific events occur, used to enforce minimum cardinality for children and parents