Modern Systems Analysis and Design

1
Modern Systems Analysisand Design
???? ???? ?????? ?????? ?????? ?????
???? ???? ????? ???

• 12
• ????? ????? ????????
• Designing Databases

12.1
2
????? ?????

• ????? ?? ?? ??? ????????? ?????? ?????? ????????
• Relation
• Primary key
• Normalization
• Functional dependency
• Foreign key
• Referential integrity
• Field
• Data type
• Null value
• Denormalization
• File organization
• Index
• Secondary key

12.2
3
????? ?? ????? ????? ????????Purpose of Database
Design

• ????? ???????? ???? ????? ???? ????? ??????
• ????? ??? ????? ?? ?????
• ??? ????? ???? Minimal redundancy
• ????? ????? ????? ?????? ???????? ? ????
  ????????? ???????? ????????
• ????? ????? ????? ?????? ???????? ? ????? ??? ???
  ????? ??????? ?????? (?????) ????????
• ????? ?????? ???? ????? ???????? ????????? ???
  ????? ???? ?????? ?????? ???????? ???? ???????
  ??? ?????? ?????? ???????
• ?????? ????? ?????? ????? ???????? ???? ???? ????
  ???? ????? ???????? ???? ???? ????? ????

12.3
4
??????? ????? ????? ????????Process of Database
Design

• ??????? ??????? Logical Design
• ???? ??? ???? the conceptual data model
• ????? ???? ??????-
• ????? ???? ????? ???????? (????? ????) ??? ?????
  ?????? (???? ?????) ???????? ??????? ????????
  normalization principles
• ????? ????????? ???????? ???????? (???????) ??
  ????? ?????? ????? ??????
• ????? ?????? ????? ???????? ?????????? ?????? the
  conceptual E-R data model ??? ??????? ??????
  ?????? (?????)
• ???? ??? ????? ????? ???????? ??????? (???? 2) ??
  ????? ???????? ?????????? ?????? (???? 3) ?????
  ?????? ???? ????? ?????? ????? ???????? ???????

12.4
5
??????? ????? ????? ????????Process of Database
Design

• ??????? ????? ??????? Physical Design
• ???? ??? ???? logical database design
• ?????? ????
• ?????? ??????? ??????? ??????? ??? ???? ?? ????
  ????? ???????? ???????
• ????? ??????? attributes (??????) ?? ???? ?????
  ???????? ??????? ?? ??? ????? ????? physical
  records
• ????? ??????? ??? ??????? ????? ?? ????? ?????
  (??? ??? ????) ???????? ???? ??????? ???? ??????
• ???? ??????? ??????? ???? ?? ???? ????? ????????
  ???? ?????? ????? ???? ?????
• ??????? ?????? ??? ?? ???? ???? ?? ???? ????? ??
  ????? ?? ??????
• ???????? ???????? (????????) ?? ?????? ???????
• ??????? ?????? ?????? ???????? ???? ??? ?????
  ???????? ??? ?????

12.5
6
????? ????? ???????? ????????? Relational
Database Model

• ???????? ???? ?? ??????? ?? ??????? ?? ????????
• ??????? Relation
• ????? ????? ??? ?????? ?? ????????? ?? ?? ????? .
  ?? ????? ????? ?? ?????? ?? ??????? (??????)
  ??????? ?????? ?? ??????
• ?????? Properties
• ?? ??? ?? ?? ???? ???? unique
• ????? ?????? ??? ???
• ???? ????? ?????? ????? ?????? ???? ????? ??
  ?????
• ??????? ??????? ???? ??? ??-
• Well-Structured Relation
• ???? ???? ???? ???? ?? ??????? ????? ????????
  ?????? ??????? ??????? ?????? ???? ????? ?? ???
  ????? ?? ?????? ????

12.6
7
?????? ???????Normalization

• ?? ????? ????? ???? ?????? ???? ??? ???? ????
  ??????
• ??????? ?????? ???????Second Normal Form (2NF)
• ?? ??? ????? ???? (??? ????? nonprimary ) ????
  ?????? ??????? ??????? ????? (full functional
  dependency)
• ??????? ?????? ??????? Third Normal Form (3NF)
• ?????? ????? ??????? ?? ????? ??? ????? (called
  transitive dependencies)
• ????? ????? ?????? ??????? ?? ?? ?? ??? ????? ???
  ????? ??? ????? ??? ?? ??????? ???????
• ?????? - ??????? ??????? PK ?? ????? ?? ???? ??
  ??? ? ???????? ????? ????? ?? Indexed

12.7
8
?????????? ???????? ????????? ????????Functional
Dependencies and Primary Keys

• ?????????? ???????? Functional Dependency
• ????? ????? ??? ?????? ??? ???? ?????? ????????
  ?????? ????? A?? ???? ???? ???? ?????? ?????? B
• ????? ???? ????? ??????? ?? ??????? ?? ???????
  ?????? ?????? ???? ????? ???????? ???? ???? ????
  ?????? ???????
• ??????? ??????? Primary Key
• ?? ??? ????? ???? ????? ????? ???? ??????
  ????????

12.8
9
?????????? ???????? ????????? ????????Functional
Dependencies and Primary Keys

• ??????? ?????? ???????Second Normal Form (2NF)
• ??? ??????? ???? ?? ????-
• ??????? ??????? ????? ?? ??? ?? ??? ????
• ?? ???? ?????? ??? ?????? ?? ???????
• ?? ????? ??? ????? ????? ??? ?????? ???? ??????
  ??????
• ??????? ??????? ?????? 2NF
• ??? ??????? ??? ?????? ????? ??????? ???? ????
  ??????
• ???????? ???? ??????? ??????? ?? ??????? ???????
• ??????? ??????? ??????Third Normal Form (3NF)
• ??????? ???? ?? ?????? ?? ????? ?????? ????? ????
  ?? ????? ?????? ???? ?? ???? ????? ?????? ???
  ????? ?? ???? ?? ???????? ??? ????????

12.9
10
?????????? ???????? ????????? ????????Functional
Dependencies and Primary Keys

• ?????? ??????? Foreign Key
• ??? ???? ?????? ??? ????? ?? ????? ??????? ?????
  ?? ??? ??? ?? ????? ????? ?? ???????
• ??????? ??????? Referential Integrity
• ??? ?? ??????? ???? ?? ?????? ???? ?????? ?????
  ??? ???? ???? ??? ?? ??? ?????????

12.10
11
????? ?????? ???????? ?????????? ??? ??????
Transforming E-R Diagrams into Relations

• ???????
• ??? ???????? Represent entities
• ??? ???????? Represent relationships
• ???? ???????? ?????? Normalize the relations
• ???? ???????? Merge the relations
• ????? ???????? Represent Entities
• Each regular entity is transformed into a
  relation
• The identifier of the entity type becomes the
  primary key of the corresponding relation
• ??????? ??????? ??? ?? - ( ???? ???? ???? ??? ??
  ?? ??? ??? ?? ?? ????? ??????? ??????? ?? ????
  ??? )

12.11
12
Transforming E-R Diagrams into Relations

• ????? ???????? Represent Relationships
• Binary 1N Relationships
• Add the primary key attribute (or attributes) of
  the entity on the one side of the relationship as
  a foreign key in the relation on the right side
• The one side migrates to the many side
• Binary or Unary 11
• Three possible options
• Add the primary key of A as a foreign key of B
• Add the primary key of B as a foreign key of A
• Both of the above

12.12
13
Transforming E-R Diagrams into Relations

• Represent Relationships (continued)
• Binary and Higher MN relationships
• Create another relation and include primary keys
  of all relations as primary key of new relation
• Unary 1N Relationships
• Relationship between instances of a single entity
  type
• Utilize a recursive foreign key
• A foreign key in a relation that references the
  primary key values of that same relation
• Unary MN Relationships
• Create a separate relation
• Primary key of new relation is a composite of two
  attributes that both take their values from the
  same primary key

12.13
14
12.14
15
Transforming E-R Diagrams into Relations

• ??? ????????Merging Relations (View Integration)
• ?????? ???????
• ?????????? Synonyms(????? ??????? ???? ?????)
  ??? ????? ????????
• ?????????? Homonyms (??? ???? ?????? ?????? )
  ???? ??? ???
• ?????? ??? ???? ???? ?????? Dependencies between
  nonkeys
• ???? ?? ??? ????? ????????

12.15
16
????? ????? ???????? ????????Physical File and
Database Design

• ????????? ??????? ??????-
• ?????? ?????? ??? ???? ??? ?????? ????????
• ????? ??? ??? (???? ? ???? ? ..???)
• ??? ???????? ??? ???? ?????? ????? ??????? ?????
  ?? ???? ??? ?? ??? ???? ???? ???
• ??????? ???????? ????? ???? ???? ??? ???????? ??
  ???
• ??? ??????????? ????????? ?? ???

12.16
17
????? ?????? Designing Fields

• ????? Field
• ???? ???? ?????? ???? ????????
• ?? ??? ?? ?? ????? ????? ??? ??? ?? ????
• ?????? ????? ???????? data types
• ??? ?????? Data Type
• ????? ???? ????????
• ????? ?????
• ????? ????? ???????
• ????? ?? ????? ??????? ???? ?????
• ????? ????? ????????
• ??? ????? ?????? ????????
• ?????? ???????? Calculated fields

12.17
18
??? ?????? ?? ????? ????????Methods of
Controlling Data Integrity

• ?????? ??????? ?? ?????????? Default Value
• ???? ????? ??? ?????? ??? ?? ?? ???? ?? ??? ????
  (??? ????? ?????)
• ????? Range Control
• ??? ???? ?? ????? ?????? ??? (???? ?? 1-10 ?? ???
  2?3?4)
• ??????? ??????? Referential Integrity
• ??? ?????? ????? ?????? ??? ???? ?? ??? ????
  (????? ???????? ???)
• ?? ???? ???? Null Value
• ??? ????? ??????? ?? ??? ??? ???? ? ??????? ???
  ???? ????

12.18
19
????? ??????? ???????? - Designing Physical Tables

• ????? ???????? ????????? ?? ?????? ?? ???????
  ?????????
• ?????? (???? ?? ?????? ???????? ???? ?????? ??
  ?? ??)
• ????? ???????
• ????????? ?????? ?????? ??????? (??????? ???????
  ?? ??????) ??? ??? ???????? ??????? ???
• ???????? ?????? ???????? (???? ??? ???????
  ????????)
• ??? ??????? ??? ???????? Denormalization
• ????? ?? ????? ?????? ?????? ????? ??? ???? ???
  ??????? ???????
• ??????? Partitioning (?????? ??? ????? ???? ???
  ????? ??????)
• ?????? ?????? ????? ??? ???? ????

12.19
20
????? ??????? ???????? Designing Physical
Tables

• ????? ??????? Denormalization
• 3 ?????
• Two entities with a one-to-one relationship
• A many-to-many relationship with nonkey
  attributes
• Reference data
• Arranging Table Rows
• ????? ??????? (???? ???? ???????)
• ??? ???? ?????? ????? ?????? ?? ???? ??????
  ???????
• ?? ???? ??? ????? ?? ????? ???????? ???? ??? ????
  ?????

12.20
21
????? ??????? ????????Designing Physical Tables

• ????? ????? File Organization
• ????? ?????? ??????? ?????? ?? ???
• ????? ??? ???????
• ???? ???? ????????
• ?????? ???? ????????
• ??????? ???? ??????? ???????
• ??????? ?? ??? ???????? ?? ??????
• ????? ?????? ?????? ???????
• ????? ????? ?????
• ??????? ?? ????? ?????????

12.21
22
????? ??????? ????????Designing Physical Tables

• ????? ???????
• ?????? Sequential (??? ??????? ??????? ?????
  ???????? ????? ????? ????? ????? ??? ????? ????
  ????)
• ????? Indexed (????? ??? ?? ???? ?????? ???????
  ??????? ??????? ?? ??? ?????? ?????? ???????)
• ?????? Index
• A table used to determine the location of rows in
  a file that satisfy some condition
• ?????? ??????? Secondary Index
• Index based upon a combination of fields for
  which more than one row may have same combination
  of values

12.22
23
12.23
24
????? ????? ?????? Designing Controls for Files

• ?????? ????? ????????? Backup Techniques
• Periodic backup of files
• Transaction log or audit trail
• Change log
• ?????? ????? ???????? Data Security Techniques
• Coding or encrypting
• User account management
• Prohibiting users from working directly with the
  data. Users work with a copy which updates the
  files only after validation checks

12.24