Modern Systems Analysis and Design

[Audio] Modern Systems Analysis and Design 9th Edition Chapter 7a Designing Databases.

[Audio] Learning Objectives 9.1 Describe the database design process, its outcomes, and the relational database model 9.2 Describe normalization and the rules for second and third normal form 9.3 Transform an entity-relationship (E-R) diagram into an equivalent set of well-structured (normalized) relations 9.4 Merge normalized relations from separate user views into a consolidated set of well-structured relations 9.5 Describe physical database design concepts including choosing storage formats for fields in database tables, translating well-structured relations into efficient database tables, explaining when to use different types of file organizations to store computer files, and describing the purpose of indexes and the important considerations in selecting attributes to be indexed.

[Audio] Introduction Database design has five purposes as follows: Structure the data in stable structures (normalize) Develop a logical database design that reflects the actual data requirements that exist in the forms Develop a logical database design as a basis for physical database design Translate a relational database model into a technical file and database design that balances several performance factors Choose data storage technologies that will efficiently, accurately, and securely process database activities.

[Audio] Figure 9-1: Systems Development Life Cycle with Design Phase Highlighted.

[Audio] Database Design 9.1 Describe the database design process, its outcomes, and the relational database model File and database design occurs in two steps: Develop a logical database model, which describes data using notation that corresponds to a data organization used by a database management system Relational database model Prescribe the technical specifications for computer files and databases in which to store the data Physical database design provides specifications Logical and physical database design in parallel with other system design steps.

[Audio] . Conceptual Model Design Sales Store Logical Model Design Physical Model Design Date Decreti«t Morth DescriptÉn Year Wee Sal U store (FK) Product 10 Product Ozcription Category Descrötion I-hit Price cnr€ß€sc: VARCHAR(30) YONTH_DESC: VARCHAR(3 YEAR : rt4TEGER INTECR WEEXJESC: VARCHAR(30) FACT SALES PRODUCT MEER ROOYESC: VARCHAR(SO) CATEGORY _ID; INTEGER CATEGORY _OESC: V CREATED: OATE Product (R Dita (FK) Iten: Sold S Arncant Decription Name Created INTEGER OATE_IO; INTER ITEMS_SCO: INTEGER FLOA STC*E9ESC' VARCHAR(SO) VARCHAR(SO CREATED:.

[Audio] Figure 9-2: Relationship Between Data Modeling and the SDLC.

[Audio] Process of Database Design 9.1 Describe the database design process, its outcomes, and the relational database model Four key steps in logical database modeling and design: Develop a logical data model for each known user interface for the application using normalization principles Combine normalized data requirements from all user interfaces into one consolidated logical database model (view integration) Translate the conceptual E-R data model for the application into normalized data requirements Compare the consolidated logical database design with the translated E-R model and produce one final logical database model for the application.

[Audio] Physical Database Design Key physical database design decisions: Choosing a storage format (data type) for each attribute from the logical database model Grouping attributes from the logical database model into physical records Arranging related records in secondary memory (hard disks and magnetic tapes) so that records can be stored, retrieved and updated rapidly Selecting media and structures for storing data to make access more efficient.

[Audio] Deliverables and Outcomes During logical database design you must account for every data element on a system input or output Normalized relations are the primary deliverable Primary key – attribute (or combination of attributes) whose value is unique across all occurrences of a relation Physical database design converts relations into database tables Programmers and database analysts code the definitions of the database using Structured Query Language (SQL).

[Audio] Figure 9-3(d): Conceptual Data Model and Transformed Relations.

[Audio] The Relational Database Model (1 of 2) 9.1 Describe the database design process, its outcomes, and the relational database model Relational database model – data represented as a set of related tables or relations Relation – named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows..

[Audio] . Table also called Relation Primary Key Domain Ex: NULL CustomerlD CustomerName 1 Google 2 Amazon 3 Apple O guru99.com Status Active Active Inactive Tuple OR Row Total # of rows is Cardinality Column OR Attributes Total # of column is Degree.

[Audio] The Relational Database Model (2 of 2) 9.1 Describe the database design process, its outcomes, and the relational database model Relations have several properties that distinguish them from nonrelational tables: Entries in cells are simple Entries in columns are from the same set of values Each row is unique The sequence of columns can be interchanged without changing the meaning or use of the relation The rows may be interchanged or stored in any sequence.

[Audio] Well-Structured Relations 9.1 Describe the database design process, its outcomes, and the relational database model Well-structured relation – relation that contains a minimum amount of redundancy and that allows users to insert, modify, and delete the rows without error or inconsistencies; also known as a table.

[Audio] Normalization (1 of 2) 9.2 Describe normalization and the rules for second and third normal form Normalization – process of converting complex data structures into simple, stable data structures The result of normalization is that every nonprimary key attribute depends upon the whole primary key and nothing but the primary key.

[Audio] Normalization (2 of 2) First Normal Form (1NF): Has no multivalued attributes, unique rows, and all relations are in 1NF Second Normal Form (2NF): Each nonprimary key attribute is identified by the whole key (referred to as a full functional dependency) Third Normal Form (3NF): Nonprimary key attributes do not depend on each other (referred to as a transitive dependency).

[Audio] Functional Dependency (1 of 2) Functional dependency – constraint between two attributes in which the value of one attribute is determined by the value of another attribute Example of attribute B being functionality dependent on attribute A Dependency is represented by an arrow (  ) Attribute B is functionally dependent on attribute A if, for every valid value of A, that value of A uniquely determines the value of B Represented as: A  B.

[Audio] Functional Dependency (2 of 2) Functional dependency is not a mathematical dependency Instances (or sample data) in a relation do not prove the existence of a functional dependency Knowledge of problem domain is most reliable method for identifying functional dependency.

[Audio] Second Normal Form (2NF) Second normal form (2NF) – relation is in second normal form if every nonprimary key attribute is functionally dependent on the whole primary key To convert a relation into 2NF, decompose the relation into new relations using the attributes, called determinants, that determine other attributes The determinants are the primary keys of the new relations.

[Audio] Third Normal Form (3NF) (1 of 2) Third normal form (3NF) – relation is in second normal form and has no functional (transitive) dependencies between two (or more) nonprimary key attributes.

[Audio] Figure 9-9: Removing Transitive Dependencies (a) Relation with Transitive Dependency (b) Relation in 3NF.

[Audio] . SALESI Custorner 10 8023 9167 7924 6837 8596 7018 Customer_Name Tuc*er Ec*ersley Salesperson Hernandez SPERSON S Jesperson Smith Hemandez Faub Customer_Name, SalesPerson) Functional dependencies: • Customer ID -5 Customer Name, SalesPerson SPERSON(SalesPerson, egion) Functional dependency: • SalesPerson -Y Region.

[Audio] Third Normal Form (3NF) (2 of 2) 9.2 Describe normalization and the rules for second and third normal form Foreign key – attribute that appears as a nonprimary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation Referential integrity – rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null (i.e., have no value).

[Audio] Transforming E-R Diagrams into Relations 9.3 Transform an entity-relationship (E-R) diagram into an equivalent set of well-structured (normalized) relations Transforming an E-R diagram into normalized relations and merging all of them into one consolidated set of relations takes four steps: Represent entities (each becomes a relation) Represent relationships (each must be represented in the relational database design) Normalize the relations (make them well structured) Merge the relations (renormalize if necessary to remove redundancy).

[Audio] Representing Entities Each regular entity is transformed into a relation The identifier of the entity type becomes the primary key of the corresponding relation The primary key must satisfy the following two conditions The value of the key must uniquely identify every row in the relation The key should be nonredundant The entity type label is translated into a relation name.

[Audio] Representing Relationships (1 of 5) The procedure for representing relationships depends on: The degree of the relationship (unary, binary, ternary) The cardinality of the relationship Binary 1:N Relationship – represented by adding the primary key attribute (or attributes) of the entity on the one side of the relationship as a foreign key in the relation that is on the many side of the relationship.

[Audio] Representing Relationships (2 of 5) Binary or Unary 1:1 relationship is represented by any of the following: 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.

[Audio] Figure 9-11: Representing a 1:N Relationship (a) E-R Diagram (b) Relations.

[Audio] Representing Relationships (3 of 5) 9.3 Transform an entity-relationship (E-R) diagram into an equivalent set of well-structured (normalized) relations Binary and Higher-Degree M:N Relationships – represented by creating another relations, include the primary keys of all relations into the new one as a primary key Becomes a composite key Any non-key attributes associated with the M:N relationship are included in the new relation.

[Audio] Figure 9-12: Representing an M:N Relationship (a) E-R Diagram (b) Relations.

[Audio] Representing Relationships (4 of 5) 9.3 Transform an entity-relationship (E-R) diagram into an equivalent set of well-structured (normalized) relations Unary 1:N Relationship (also called recursive relationships): Is modeled as a relation Primary key of that relation is the same as for the entity type Foreign key is added to the relation that references the primary key values Recursive foreign key – foreign key in a relation that references the primary key values of that same relation.

[Audio] Representing Relationships (5 of 5) Unary M:N Relationship is modeled as one relation, then: Create a separate relation to represent the M:N relationship The primary key of the new relation is a composite key of two attributes that both take their values from the same primary key Any attribute associated with the relationship is included as a nonkey attribute in this new relation.

[Audio] Figure 9-13: Two Unary Relationships (a) EMPLOYEE with Manages Relationship (1:N) (b) Bill-of-Materials Structure (M:N).

[Audio] Table 9-1: E-R Diagrams to Relational Transformation E-R Structure Relational Representation Regular entity Create a relation with primary key and nonkey attributes. Weak entity Create a relation with a composite primary key (which includes the primary key of the entity on which this weak entity depends) and nonkey attributes. Binary or unary 1:1 relationship Place the primary key of either entity in the relation for the other entity or do this for both entities. Binary 1:N relationship Place the primary key of the entity on the one side of the relationship as a foreign key in the relation for the entity on the many side. Binary or unary M:N relationship or associative entity Create a relation with a composite primary key using the primary keys of the related entities, plus any nonkey attributes associative entity of the relationship or associative entity. Binary or unary M:N relationship or associative entity with additional key(s) Create a relation with a composite primary key using the primary keys of the related entities and additional primary key attributes associated with the relationship or associative entity, plus any nonkey attributes of the relationship or associative entity. Binary or unary M:N relationship or associative entity with its own key Create a relation with the primary key associated with the relationship or associative entity, plus any nonkey attributes of the relationship or associative entity and the primary keys of the related entities (as foreign key attributes). Supertype/subtype Create a relation for the superclass, which contains the primary relationship key and all nonkey attributes in common with all subclasses, plus create a separate relation for each subclass with the same primary key (with the same or local name) but with only the nonkey attributes related to that subclass..

[Audio] Merging Relations 9.4 Merge normalized relations from separate user views into a consolidated set of well-structured relations Merging relations Is the last step in the logical database design Purpose is to remove redundant relations Example when given two relations: EMPLOYEE1(Emp_ID,Name,Address,Phone) EMPLOYEE2(Emp_ID,Name,Address,Jobcode,Number_of_Years) They can be merged together: EMPLOYEE(Emp_ID,Name,Address,Phone,Jobcode,Number_of_Years).

[Audio] View Integration Problems (1 of 4) Must understand the meaning of the data and be prepared to resolve any problems that arise in the process Synonym – two different names used for the same attribute When merging, get agreement from users on a single, standard name Example of two relations with synonym primary keys (representing SSN numbers) of different names: STUDENT1(Student_ID,Name) STUDENT2(Matriculation_Number,Name,Address).

[Audio] View Integration Problems (2 of 4) Homonym – single attribute name that is used for two or more different attributes Resolved by creating a new descriptive name Example: home address vs. local address? STUDENT1(Student_ID,Name,Address) STUDENT2(Student_ID,Name,Phone_Number,Address).

[Audio] View Integration Problems (3 of 4) Dependencies between nonkeys occurs when two 3NF relations are merged to form a single relation such as: STUDENT1(Student_ID,Major) STUDENT2(Student_ID,Adviser) Since both have the same primary key they can be merged as follows: STUDENT(Student_ID,Major,Adviser) If a transitive dependency exists such as Major  Advisor You need to normalize to remove the transitive dependency STUDENT(Student_ID,Major) MAJOR ADVISER(Major,Adviser).

[Audio] View Integration Problems (4 of 4) Class/Subclass relationships may be hidden in user views or relations. Consider the following: PATIENT1(Patient_ID,Name,Address,Date_Treated) PATIENT2(Patient_ID,Room_Number) What if PATIENT can refer to both inpatient and outpatient? Then what? The answer? Convert it to a Supertype/Subtype! PATIENT(Patient_ID,Name,Address) INPATIENT(Patient_ID,Room_Number) OUTPATIENT(Patient_ID,Date_Treated).

[Audio] Figure 9-16: E-R Diagram Corresponding to Normalized Relations of Hoosier Burger’s Inventory Control System.

[Audio] Final Normalized Relations for Hoosier Burger 9.4 Merge normalized relations from separate user views into a consolidated set of well-structured relations Final normalized relations for Hoosier Burger: SALE(Receipt_Number,Sale_Date) PRODUCT(Product_ID,Product_Description) INVOICE(Vendor_ID,Invoice_Number,Invoice_Date,Paid?) INVENTORY ITEM(Item_Number,Item_Description,Quantity_in_ Stock,Minimum_Order_Quantity,Type_of_Item) ITEM SALE(Receipt_Number,Product_ID,Quantity_Sold) INVOICE ITEM(Vendor_ID,Invoice_Number,Item_Number,Quantity_Added) RECIPE(Product_ID,Item_Number,Quantity_Used) VENDOR(Vendor_ID,Vendor_Name).

[Audio] Physical File and Database Design 9.5 Describe physical database design concepts including choosing storage formats for fields in database tables, translating well-structured relations into efficient database tables, explaining when to use different types of file organizations to store computer files, and describing the purpose of indexes and the important considerations in selecting attributes to be indexed Designing physical files/databases requires the following information: Normalized relations, including volume estimates Definitions of each attribute Descriptions of where and when data are used: entered, retrieved, deleted, and updated (including frequencies) Expectations or requirements for response time and data integrity Descriptions of the technologies used for implementing the files and database so that the range of required decisions and choices for each is known.

[Audio] Designing Fields Field – smallest unit of named application data recognized by system software An attribute from a relation is now recognized as a field in a database Data type – coding scheme recognized by system software for representing organizational data.

[Audio] Choosing Data Types Selecting a data type requires balancing four objectives: Minimize storage space Represent all possible values of the field Improve data integrity of the field Support all data manipulations desired on the field.

[Audio] Table 9-2: Commonly Used Data Types in Oracle 10g Data Type Description VARCHAR2 Variable-length character data with a maximum length of 4,000 characters; you must enter a maximum field length (e.g., VARCHAR2(30) for a field with a maximum length of 30 characters). A value less than 30 characters will consume only the required space. CHAR Fixed-length character data with a maximum length of 255 characters; default length is 1 character (e.g., CHAR(5) for a field with a fixed length of five characters, capable of holding a value from 0 to 5 characters long). LONG Capable of storing up to two gigabytes of one variable-length character data field (e.g., to hold a medical instruction or a customer comment). NUMBER Positive and negative numbers in the range 10–130 to 10126; can specify the precision (total number of digits to the left and right of the decimal point) and the scale (the number of digits to the right of the decimal point) (e.g., NUMBER(5) specifies an integer field with a maximum of 5 digits and NUMBER( 5, 2) specifies a field with no more than five digits and exactly two digits to the right of the decimal point). DATE Any date from January 1, 4712 BC to December 31, 4712 AD; date stores the century, year, month, day, hour, minute, and second. BLOB Binary large object, capable of storing up to four gigabytes of binary data (e.g., a photograph or sound clip)..

[Audio] Calculated Fields 9.5 Describe physical database design concepts including choosing storage formats for fields in database tables, translating well-structured relations into efficient database tables, explaining when to use different types of file organizations to store computer files, and describing the purpose of indexes and the important considerations in selecting attributes to be indexed Calculated field – field that can be derived from other database fields. Also known as a computed field or a derived field. It is common for an attribute to be mathematically related to other data The database will either stored or compute the calculated field when requested.

[Audio] Controlling Data Integrity Default value – value a field will assume unless an explicit value is entered for that field Range control – limits values (numeric or alpha-numeric data) that can be entered into a field Referential integrity – constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation Null value – special field value, distinct from zero, blank, or any other value, that indicates that the value for the field is missing or otherwise unknown.

[Audio] Figure 9-17: Examples of Referential Integrity Field Controls (a) Referential Integrity Between Relations (b) Referential Integrity Within a Relation.

[Audio] Designing Physical Tables (1 of 3) 9.5 Describe physical database design concepts including choosing storage formats for fields in database tables, translating well-structured relations into efficient database tables, explaining when to use different types of file organizations to store computer files, and describing the purpose of indexes and the important considerations in selecting attributes to be indexed A relational table is a set of related tables related by foreign keys referencing primary keys Physical table – named set of rows and columns that specifies the fields in each row of the table Denormalization – process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields.