System Design

1. System Analysis and Design System Design - Mr. Ahmad Al-Ghoul

2. learning Objectives • Explain data design terminology, including entitles, fields, records, files, tables, and key fields System Analysis and Design System Design

3. Data Design Terminology • Data model • A formal way of representing the data that are used and created by a business system • Shows the people, places and things about which data is captured and the relationships among them. • Logical data model • shows the organization of data without indicating how it is stored, created, or manipulated. System Analysis and Design System Design

4. Data Design Terminology • Entity: An entity is a person, place, thing, or event for which data is collected and maintained. • Must be multiple occurrences to be an entity eg. EMPLOYEE SALES ORDER SUPPLIER • often identified from nouns used within the business application System Analysis and Design System Design

5. Data Design Terminology • Entity Type • An Entity Type is a logical grouping of data relevant to the application in question • An Entity instance is an Occurrence of an Entity Type • An Entity Type would be represented in a RDBMS as a table. • An Entity instance would be represented as a single row in a table System Analysis and Design System Design

6. Data Design Terminology • Table or file: A set of related records is grouped into a table, which stores data about a specific entity. Tables are shown as two-dimensional structures that consist of vertical columns representing fields and horizontal rows representing records. • Field: A single characteristic or fact about an entity. A field, or attribute, is the smallest piece of data that has meaning within an information system. For example, a Social Security number or company name could be examples of a field. The terms, data element and field, are used interchangeably. • Attribute names are nouns • Sometimes entity name is added at the beginning of the attribute name for clarity System Analysis and Design System Design

7. Data Design Terminology • Common field: A common field is an attribute that appears in more than one entity. Common fields can be used to link entities in various types of relationships. • Record: also called a tuple, is a set of related fields that describes one entity instance, or member of an entity, such as one customer, one order, or one product. A record might have one or dozens of fields, depending on what information is needed. System Analysis and Design System Design

8. Data Design Terminology • Key Fields • Key fields are used during the systems design phase to organize, access, and maintain data structures. The four types of key fields are primary keys, candidate keys, foreign keys, and secondary keys. System Analysis and Design System Design

9. Data Design Terminology • Key Fields • Primary key: A primary key is a field or combination of fields that uniquely and minimally identifies a particular member of an entity. An identifier may be ‘artificial,’ such as creating an ID number. For example, in a student table the student number is a unique primary key because no two students can have the same student number. That key also is minimal because it contains no information beyond what is needed to identify the student. Sometimes it is necessary for a primary key to consist of a combination of fields. In that case, the primary key is called a combination key, composite key, concatenated key, or multi-valued key. For example, if a student registers for three courses, his or her student number will appear in three records so its no more valid as a primary key. So in the registration file, neither the student number nor the course ID is unique, to identify a specific student in a specific course, the primary key must be a combination of student number and course ID • No two fields within the primary key can have the same value System Analysis and Design System Design

10. Supplier Number Supplier Name Supplier Address Supplier Tel. No. Sup Con 1463 Ahmad 115 zeriab St +962/7563025 Ali 3621 Ali & sons 12 King Hussien St 020-8… ... 2327 Bella Sonic Lake Industrial Estate +962/7999663 ... 6762 … 3 Lot’s Corner ... ... Data Design Terminology Attribute Names Primary Key Columns Field Record System Analysis and Design System Design

11. Data Design Terminology • Key Fields • Candidate key: Sometimes it is possible to have a choice of fields or field combinations to use as the primary key. Any field that could serve as a primary key is called a candidate key. For example, if every employee has a unique employee number, then you could use either the employee number or the Social Security number as a primary key. • Nonkey field: Any field that is not a primary key or a candidate key is called a nonkey field. System Analysis and Design System Design

12. Data Design Terminology • Key Fields • Foreign key: A foreign key is a field in one table that must match a primary key value in another table in order to establish the relationship between the two tables. Unlike a primary key, a foreign key need not be unique • A foreign key must either match a primary key or else be null • Secondary key: is a field or combination of fields that can be used to access or retrieve records. Secondary key values are not unique. For example, if you need to access records for only those customers in a specific ZIP code, you would use the ZIP code field as a secondary key. Secondary keys also can be used to sort or display records in a certain order. System Analysis and Design System Design

13. Data Design Terminology • Entity Integrity • Entity integrity constraints are rules for primary keys: • The primary key cannot have a null value • If the primary key is a composite key, none of the fields in the key can contain a null value System Analysis and Design System Design

14. Data Design Terminology • Referential Integrity • Validity checks can help avoid data input errors • One type of validity check, called referential integrity, is a set of rules that a voids data inconsistency and quality problems • In a relational database, referential integrity means that a foreign key value cannot be entered in one table unless it matches an existing primary key in another table • You cannot change a primary key that has matching child table records • A child table that has a foreign key for a different record • Referential integrity also can prevent the deletion of a record has a primary key that matches foreign keys in anther table System Analysis and Design System Design

15. Data Design Terminology Secondary key Foreign key Primary keys Secondary key Candidate keys Combination primary key System Analysis and Design System Design

16. Sequence Summary • In an information system, an entity is a person, place, thing, or event for which data is collected and maintained • A field, or attribute, is a single characteristic of an entity • A record, or tuple, is a set of related fields that describes one instance of an entity • Records are grouped into files (in a file-oriented system) and tables (in a database environment) • A primary key is a field or field combination that uniquely and minimally identifies a specific record; a candidate key is any field that could serve as a primary key • A foreign key is a field or field combination that must match primary key of anther file or table • A secondary key is a field or field combination used as the basis for sorting or retrieving records • Entity integrity constraints are rules for primary keys • referential integrity, is a set of rules that a voids data inconsistency and quality problems System Analysis and Design System Design

17. Sequence Summary • In this Sequence we have • Explain data design terminology, including entitles, fields, records, files, tables, and key fields • Focused on entity integrity and referential integrity System Analysis and Design System Design

18. Reference [1] System Analysis and Design, Sixth Edition Authors: Gary B. Shelly, Thomas J. Cashman and Harry J. Rosenblatt Publisher: SHELLY CASHMAN SEWIES. System Analysis and Design System Design