4
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

ส่วนที่ 4 PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on
  • Presentation posted in: General

ส่วนที่ 4. System Design. การออกแบบระบบ. Chapter 13. Physical Database Design. การออกแบบฐานข้อมูลในระดับกายภาพ. กิจกรรมในขั้นตอนนี้ได้แก่ 1. การออกแบบฐานข้อมูลใน ระดับกายภาพ 2. การออกแบบ Application. System Development Life Cycle : SDLC. 13.2. Learning Objectives.

Download Presentation

ส่วนที่ 4

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


4

4

System Design


4

Chapter 13

Physical Database Design


4

1.

2. Application

System Development Life Cycle : SDLC

13.2


4

Learning Objectives

  • (Physical Database Design)

  • Logical Schema Table

  • Microsoft Access

13.3


4

Topics

  • Logical Schema Table

  • Microsoft Access

13.4


4

  • Conceptual Logical

    • Relation Table

    • Table

    • (Integrity Rules)

    • (File Organization)

13.5


4

  • (Bit)

  • (Byte)

  • (Field)

  • (Record)

  • (File)

  • (Database)

13.6


4

(Bit)

(Binary Digit) 0 1

(Byte)

(Bit) 0 1 1 8 0 1 1 256

(Field)

Cust_Id , Cust_name

13.7


4

(Record)

-

(File)

(Database)

13.8


4

13.9


4

FILE

RECORD

RECORD

RECORD

...

RECORD

FILED

FILED

FILED

BYTE

BYTE

BIT

BIT

13.10


4

  • fields

13.11


4

13.12


4

Create Table factory

( fac_idint not null,

fac_namevarchar(25) not null,

zonevarchar(10) ,

Constraint pk_fac_id primary key (fac_id));

Create table car

(car_idint not null,

car_namevarchar(25)not null,

seriesvarchar(25)not null,

colorvarchar(10),

pricereal,

car_datedatetime,

fac_idint,

Constraintpk_car_id primary key (car_id),

Constraintak_car_name unique(car_name),

Constraintak_series unique(series),

Constraintch_color check(color in('red','black','blue','green')),

Constraintch_price check(price between 100000 and 500000),

Constraintch_date check(car_date < getdate()),

Constraintfk_fac_id foreign key (fac_id) references factory);

13.13


4

Logical Schema Table

Physical

  • Logical Schema Table

13.14


4

Logical Schema Table

2

  • Table

  • Integrity Rules

13.15


4

Table

Table Relation

Relational Schema

Logical Table Table

  • Table

  • Attribute Table

  • (Data Type) Attribute

  • Attribute

  • Attribute Primary Key ,Foreign Key Alternate Key

  • Integrity Rule Primary Key Foreign Key

13.16


4

Table

Employee(Emp-No,Name,Address,Sex,Salary,DeptNo)

13.17


4

Table

13.18


4

Table

SQL Table

CREATE TABLE Employee (EmpNo Char(5),

NameVARCHAR (25),

Address VARCHAR (30),

SexCHAR(1),

SalaryCURRENCY,

DeptNoCHAR(3))

PRIMARY KEY (EmpNo);

13.19


4

Integrity Rules

2

  • Entity Integrity Rule IntegrityRule Attribute SQL

  • Referential Integrity Rule Integrity Rule Table SQL

13.20


4

Entity Integrity Rule

CREATE DOMAIN Sex as CHAR(1)

CHECK (VALUE IN (M, F)):

CREATE TABLE Employee (EmpNo CHAR(5) Not Null,

NameVARCHR(25)Not Null,

AddressVARCHAR(30)Not Null,

SexCHAR(1)Not Null,

SalaryCURRENCYNot Null,

DeptNoCHAR(3)Not Null,

PRIMARY KEY (EmpNo);

13.21


4

Referential Integrity Rule

CREATE DOMAIN Sex as CHAR(1)

CHECK (VALUE IN (M, F)):

CREATE TABLE Employee (EmpNo CHAR(5) Not Null,

NameVARCHR(25)Not Null,

AddressVARCHAR(30)Not Null,

SexCHAR(1)Not Null,

SalaryCURRENCYNot Null,

DeptNoCHAR(3)Not Null,

PRIMARY KEY (EmpNo);

FOREIGN KEY (DeptNo) REFERENCES

Department ON DELETE NO ACTION

ON UPDATE CASCADE;

13.22


4

  • (File organization)

  • Index Table

  • Table

13.23


4

13.24


4

(File Organization)

Heap,Hash,Idexed Sequential

Access Method (ISAM)

13.25


4

Index Table

Primary Index

Secondary Index Table

  • Primary key Primaryindex

  • Index Table

  • Attribute

  • Attribute Index

13.26


4

Table

Attribute

Derived Data Attribute Table

Table

Normalization Table

13.27


4

Table = ( Tuple * Tuble) + Index +

Table

Tuple = Attribute

Attribute= Attribute +

Attribute

13.28


4

2

  • (View)

13.29


4

  • (View) View

CREATE VIEW Staff3

AS SELECT sno,fname,address,tel_no,position,sex

FORM staff WHERE bno = B3;

13.30


4

GRANT SELECT, UPDATE,DELETE

ON staff

TO manager;

Table staff

(SELECT) (UPDATE)

(DELETE) manager

13.31


4

13.32


4

Microsoft Access

13.33


4

Reference Book and Text Book

  • Modern Systems Analysis & Design : Jeffrey A. Hoffer, Joey F.George, Joseph S. Valacich

13.34


4

Q & A

13.35


  • Login