ACCOUNTING system project PART2

User Generated

fnyznab96

Writing

Description

READ THE DOCUMENTS ATTACHED , Kim.Flowers.Procurement.2.2018F.doc IS THE DOCUMENT THAT INCLUDES THE HOME HOMEWORK THAT NEEDS TO BE DONE AMONG WITH THE STEPS TO DO IT, WHATS IS IN PAGES NUMBER 2 AND 3 IN THAT FILE IS THE SOLUTION TO PROJECT PART 1 ,THE 2 PDF FILES INCLUDES SLIDES THAT YOU WILL NEED TO COMPLETE THE PROJECT PART2. PLUS I ADDED PROJECT 1 REQUIREMENTS (WHICH I DONT WANT IT BUT I WILL POST THEM SO THINGS BE MORE CLEAR OR THEY MAY HELP YOU)

Unformatted Attachment Preview

Project Stage 2 Kim’s Flowers Expenditure Cycle The second stage of the project requires students to perform step 8 in the event-oriented modeling (as described in chapters 8 and 9). 1. Examine the standard solution for Kim Flowers. 2. In step 8 you need to convert your REA diagram into tables. Make sure that you follow the procedure described in chapter 9. 3. Implement the rules in chapter 9 for determining whether a table conforms to first, second, or third normal form. 4. Normalize tables that are not is 3rd normal form. 5. Make sure that all your tables are normalized (each table should be in 3rd normal form). 6. You should submit a list of all your tables, the attributes in each table and primary and foreign keys for each table. 7. You must show all the steps in the normalization process. Make sure that you send me your design in Word format via Email. The subject of your email must follow the required convention. The document must be named using the same convention. Your project may not be received and processed by my email account if you do not follow the naming convention. The email must be send to my MSU email from your MSU ones. 1 2 3 Logical Design for Database Systems Chapter 8 Learning Objectives  The two aspects of logical design are logical data modeling and logical process modeling.  We will look at logical data modeling using the entityrelationship (ER) approach and on Logical process modeling using data flow diagrams (DFD)  We will use business events as the foundation for our modeling From Analysis to Design    Systems analysis usually results in a narrative description of a business process scenario Business scenarios: managers supervise employees, customers place sales orders which consist of one or more items ordered, purchase orders are placed with vendors Logical modeling involves capturing the “semantics,” or meaning, of the business process scenario in a pictorial representation Fundamentals of ER modeling      Entities are represented using rectangles Relationships between entities are represented using diamonds A nything tha t the org a niz a tion would like to m a inta in inform a tion a bout is considered a n entity Entities include customers, employees, orders, payments, cash, … Relationships are nothing but associations between entities ER modeling continued… ◼ An expanded version of the initial ER diagram, showing managers supervising professionals who have skills ER modeling continued… ◼ The initial model is expanded even further, to show the jobs that professionals are assigned to, clients for whom the jobs are performed, and the equipment needed on jobs Extended Entity-Relationship (EER) model    The basic ER model shows only entities and the relationships between entities It does not show the type of relationship in terms of the cardinality of each relationship, whether relationships are mandatory or optional, and the attributes of each entity. Extended entity-relationship (EER) models show all of the above characteristics (cardinality, optionality, and attributes). Cardinality  The purpose of the "crow's foot" is to show that many professionals are supervised by a manager. Optionality An entity's participation in a relationship can either be mandatory or optional  The "optional" participation of an entity is indicated by an "O" on the relationship line leading to the entity, and the "mandatory" participation of an entity is indicated by a "|" (vertical line) on the relationship line leading to the entity  Attributes EER Conventions & Interpretation Figure Interpretation Minimum...maximum cardinality for entity "B" Every A is associated with exactly one B, every B is associated with exactly one A 1...1 Every A may be associated with one B, every B is associated with exactly one A 0...1 Every A is associated with at least one B and may be associated with many Bs, every B is associated with exactly one A 1...N Every A may be associated with either none or many Bs, every B is associated with exactly one A 0...N REA -Definitions  Resources: economic resources are the assets of the organization. They are defined as object that are both scarce and under the control of the organization  Events are phenomena that affect changes in resources  Agents are individuals that participate in an economic event Event-oriented Modeling of Business Processes, using the REA framework  McCarthy’s REA framework provides a mechanism for focusing on significant business events, the resources increased or decreased by events, and the agents who are involved with the events  Significant business events are those activities and endeavors that management wants to plan, execute, and evaluate Events are either economic or non-economic   Agents are either internal (employees) or external (customers, suppliers, stockholders, etc.) REA modeling continued..  Economic events always either increase or decrease a resource – it is an “exchange event” between the organization and an external agent  Every increase (decrease) in a resource is paired with a corresponding decrease (increase) in another resource -- this is referred to as a dua lity or “give-take” relationship  Non-economic events are those that do not directly involve an increase or decrease in resources, but have important implications for future economic events Economic Resource Economic Event Economic Agent duality Source: W. E. McCarthy “The REA Accounting Model: A Generalized Framework for Accounting Systems in a Shared Data Environment,” The Accounting Review, July 1982, pp 554-78. W.E. McCarthy “The REA Modeling Approach to Teaching Accounting Information Systems,” Issues in Accounting Education, November 2003, pp. 427-41. (source of following slides) Cookie-Monster (the customer) and Elmo (the entrepreneur) meet in the (real or virtual) marketplace, thus setting the stage for an Economic Exchange Cookie-Monster (the customer) and Elmo (the entrepreneur) engage in a SALE (transfer of Cookie Inventory) Economic Resource inside participation Economic Agent Economic Event stock-flow Economic Agent outside participation Give Take duality outside participation Economic Agent Economic Event stock-flow Economic Resource inside participation Economic Agent REA model of cookie sale from entrepreneur’s (ELMO) perspective Cookie-Monster (the customer) and Elmo (the entrepreneur) engage in a PAYMENT (transfer of Cash) Economic Resource inside participation Economic Agent Economic Event stock-flow Economic Agent outside participation Give Take duality outside participation Economic Agent Economic Event stock-flow Economic Resource inside participation Economic Agent REA model of cookie sale from entrepreneur’s (ELMO) perspective Business processes vs. information processes     The economic (exchange) event itself is a business process, but recording information pertaining to the event is an information process EXAMPLE: A sales is a business process, but recording information about the sales order is an information process also generating an invoice is information process Business process modeling is done using EER diagrams Information process modeling is done using DFDs Event-oriented modeling: a ninestep process 1. Identify significant events (what is occurring? and draw them sequentially. 2. For each event identified in step 1, identify any resources that are used by, created by, or otherwise involved with the event. There could be multiple resources associated with a single event. Resources are drawn to the left of events using the entity symbol, with a diamond connecting a resource to all event entities to which it has some relationship 3. For each event identified in step 1, identify any agents that perform the event or are otherwise affected by the event. Agents could be within the organization or outside the organization. Economic events usually have both internal and external agents associated with the event. Agents are drawn to the right of events again using the entity symbol, with a diamond connecting an agent to all event entities Event-oriented modeling: a ninestep process 4. 5. Identify relationships: Resources and agents are typically related to one another through a significant event. Additionally, it is possible that two resources or two agents have a direct relationship not stemming from any event. Specify the optionality and cardinality of relationships. Event-oriented modeling: a ninestep process 6. 7. 8. 9. Identify the attributes of events, resources, and agents Identify the information processes Design the structure of the data repository Implement the design Results of applying the above nine-step process is a tentative EER diagram and corresponding DFD – to be refined further student registration system - Example Steps 1-3 Step 4 Resource Courses Step 5 Events Offered On Course Offerings Step 6 Agents By A ssig ned to Conta in Departments E m ploy Instructors M a jor in Registration By Students Data Flow Diagrams REA diagrams shows the data structure aspect, rather than what and how modifies the entities  DFDs (Data Flow Diagrams) are used for logical process modeling  They are aimed to show the way data flows and processed  Logical Process Modeling using DataFlow Diagrams ◼ Four symbols are used, as shown below: DFDs  Data flow diagrams can be used to describe processes at different levels.  At the highest level, the DFD is called a context diagram  A context diagram treats the entire information system as one process and shows the inputs to the system from external and internal entities, and the outputs from the system to external and internal entities  Data stores shown in a context diagram because the stores are internal to the system are not Recording client jobs and the assignment of professionals to jobs ◼ The entire system is shown as one process ◼ Entities providing inputs are shown on the left and those receiving outputs are shown on the right ◼ Flow arrows indicate the type of input or output Information process modeling for student registration system  Step 7: Identify information processes  Context diagram -- Level 0 DFD ◼ One level of detail greater than the Context Diagram is shown in a Level 0 Data-Flow Diagram ◼ The breakdown of the major processes is shown ◼ Data stores being accessed and updated are shown ◼ An arrow going from a process to a data store indicates that the data store is being updated or records are being added to the data store by the process ◼ An arrow going from a data store to a process indicates that the process is reading data from the data store (but the data store is not being updated or added to) Information process modeling :: DFD    Recording process DFD ◦ Used to record all aspects of each business event identified in the REA diagram Maintenance process DFD ◦ Needed for each resource and ag ent on the REA diagram ◦ The processes involved in adding, updating, or deleting resources and agents Reporting process DFD ◦ Need one or more for each resource, event, and agent on the REA diagram ◦ Involves the extraction of information from one or more data stores to generate either routine or non-routine reports. Recording process DFDs :: rules 1. 2. 3. 4. Each event entity on the EER diagram will have a Level 0 DFD process (i.e., 1.0, 2.0, etc.), The agents associated with each event should be shown as external entity/agent symbols providing inputs to or getting output from the event process, All resource and agent entities associated with the event entity should be shown as data stores being accessed and/or updated by the event process, A data store should be shown for the event itself, with an arrow going to the data store indicating that it is being added to Level 0 DFD -- example Student Registration REA-diagram Information process modeling for student registration system  Level 0 DFD -- Level 1 DFD ◼ Showing greater detail than a Level 0 DFD, a Level 1 DFD shows details of the processes within each of the major Level 0 processes ◼ The Level 1 DFD shows the processes involved in the “assign professionals” process from the Level 0 DFD ◼ Data flows between external or internal entities must always be labeled because they indicate inputs and outputs Maintenance information processes for student registration system  Maintenance process DFD ◦ Needed for each resource and agent on the REA diagram ◦ The processes involved in adding, updating, or deleting resources and agents Reporting information processes for student registration system  Reporting process DFD ◦ ◦ Need one or more for each resource, event, and agent on the EER diagram Involves the extraction of information from one or more data stores to generate either routine or non-routine reports. S econd com prehensive exa m ple – City P ublic L ibra ry ◼ Read narrative in the chapter ◼ EER diagram -- S econd com prehensive exa m ple – City P ublic L ibra ry ◼ Context diagram -- S econd com prehensive exa m ple – City P ublic L ibra ry ◼ Level 0 DFD -- Summary  Logical data modeling using the entity relationship approach  Logical process modeling using data flow diagrams  Entity-relationship diagramming, ◦ EER  Relationship cardinality,  attributes  optional or mandatory participation in relationships  REA - focuses on the significant business events, the resources involved in these events, and the agents who perform or are affected by the events Summary Cont..  Data flow diagrams focus on processes and the logical flow of data in the system, and can focus on different levels of modeling -from context diagrams to "level 0" and "level 1" diagrams  The nine-step event-oriented modeling approach   Recording process DFDs rules Comprehensive examples the Student Registration System example and the City Public Library example Physical Design for Database Systems Chapter 9 5/13/2019 1 Summary of Chapter 8         Logical data modeling using the entity relationship approach Logical process modeling using data flow diagrams Entity-relationship diagramming was discussed, followed by extended entity relationship, which shows relationship cardinality, entity and relationship attributes, and the optional or mandatory participation in relationships Event-oriented logical modeling following the REA framework was described, which focuses on the significant business events, the resources involved in these events, and the agents who perform or are affected by the events Data flow diagrams focus on processes and the logical flow of data in the system, and can focus on different levels of modeling -- from context diagrams to "level 0" and "level 1" diagrams The nine-step event-oriented modeling approach was described Recording process DFDs rules Comprehensive examples were then presented – the Student Registration System example and the City Public Library example 5/13/2019 2 Converting an REA diagram to relational tables – Physical Design Step 8 in the event-oriented modeling approach involves the application of relatively straightforward rules for converting an REA diagram to tables.  The rules take into account (1) the optionality of relationships, and (2) the cardinality of relationships.  5/13/2019 3 Conversion rules for mandatory relationships 1. 2. 3. 4. For one-to-one relationships: collapse the entities participating in the relationship into a single table For relationships other than one-to-one relationships, create a separate table for each entity Create fields for each table based on the attributes identified in the REA diagram. Designate the primary key in each table based on the attributes identified in the REA diagram. 5/13/2019 4 Conversion rules for mandatory relationships: 5. For one to many relationships: post the primary key of the table on the "one" side of the cardinality to the table on the "many" side of the cardinality; the posted key becomes a foreign key in the table on the "many" side of the cardinality, 6. For many-to-many relationships: create a new table for the relationship and assign the primary keys of each participating entity to the new table, resulting in a composite key for the new table. Add any attributes unique to the many-to-many relationship as non-key attributes in the new table. If there are no attributes unique to the many-to-many relationship, note that the composite key table will be an "all key relation" (i.e., a table with no non-key attributes). 5/13/2019 5 SALESPERSON-PHONE SPNO Name Addre ss Dateemplo yed Depart ment Phone NO Carr ier Month lycharg e Paymentdate We can designate either SPNO or PhoneNO to be the primary key since they both uniquely identify the rows in the table 5/13/2019 6 SALESPERSONS SPNO Name Address Date-employed Department CELLULAR-PHONES PhoneNO PhoneNO * Carrier Monthly-charge Payment-date 1. A separate table was created for each entity in the REA diagram (SALESPERSONS and CELLULARPHONES). 2. Fields were created for each table based on the attributes of each entity. 3. The primary key in each table was designated (SPNO in SALESPERSONS and PhoneNO in CELLULARPHONES). 4. The primary key of the table on the "one" side of the relationship (i.e., CELLULAR-PHONES) was posted to the table on the "many" side of the relationship 5/13/2019 7 Collapse the tables together? SALESPERSON-PHONE SPNO Name Addre ss Dateemplo yed Depart ment Phone NO 5/13/2019 Carr ier Month lycharg e Paymentdate 8 SALESPERSONS SPNO Name Address Date-employed Department CELLULAR-PHONES PhoneNO Carrier Monthly-charge Payment-date 5/13/2019 SPNO 9 SALESPERSONS SPNO Name Address Date-employed Department CELLULAR-PHONES PhoneNO Carrier Monthly-charge Payment-date SP-PHONE SPNO* 5/13/2019 PhoneNO* 10 Conversion rules for optional relationships  Two rules for optional relationships 1. Consider all optional relationships as having a "many" cardinality whether or not the relationship is actually a "many" relationship and 2. Ignore the fact that relationships are optional and apply the above six rules for mandatory relationships.  the theory behind these rules is to minimize the number of "null" values in foreign key fields in the database. Null values in foreign key fields are undesirable mainly because they can make the results of queries performed on those tables unpredictable 5/13/2019 11 SALESPERSONS SPNO SPNO* Name Address Date-employed Department CELLULAR-PHONES PhoneNO Carrier Monthly-charge Payment-date 5/13/2019 12 How did we prevent null values?  Consider the alternative of collapsing both tables into one SALESPERSON-PHONE SPNO Name Addre ss Dateemplo yed Depart ment Phone NO 5/13/2019 Carr ier Month lycharg e Paymentdate 13 How did we prevent null values?   Another option would be to create two separate tables and post the key of the CELLULAR-PHONES table to the SALESPERSONS table Both alternatives would result in many null values and are therefore not desirable. SALESPERSONS SPNO Name Address Dateemployed Department PhoneNO* CELLULAR-PHONES PhoneNO Carrier Monthly-charge Payment-date 5/13/2019 14   The rule for dealing with the optional side of relationships is simply to treat the optional side as a "many" in a mandatory relationship Create a new composite key table with the primary key of both CELLULARPHONES and SALESPERSONS 5/13/2019 15  The conversion rule for dealing with optional relationships simply requires that the above relationship be treated the same way as a mandatory M:M relationship. 5/13/2019 16 SUMMARY OF CONVERSION RULES A. Mandatory relationships 1. Collapse 1:1 relationships into a single table. 2. For 1:M relationships, post the primary key of the table on the "one" side to the table on the "many" side. 3. For M:M relationships, create a new composite key table posting the primary key of each participating table to the new table. B. Optional relationships 1. Treat the "optional" side of a relationship as a mandatory "many" relationship for the purpose of conversion. 2. Apply the same conversion rules for mandatory relationships stated above. 5/13/2019 17 Application of physical modeling--the student registration system example 5/13/2019 18 Applying the conversion rules above to the above REA diagram  There are no mandatory 1:1 relationships, so we do not have to collapse any entities together A 1:M between DEPARTMENTS and COURSE-OFFERING  DEPARTMENTS,DEPT_NO*) (DEPT_NO,…..,)  COURSE-OFFERING (OFFERING-NO…., )  A 1:M between COURSES and COURSE-OFFERING  COURSE(COURSE_NO) ,COURSE_NO*)  5/13/2019 19 We therefore have the following sets of relationships  1:M between DEPARTMENTS and INSTRUCTORS ◦ DEPARTMENTS (DEPT_NO) ◦ INSTRUCTORS (INSTRUCTOR-NO, DEPT_NO*)  1:M between INSTRUCTORS and COURSE-OFFERING ◦ INSTRUCTORS (INSTRUCTOR-NO, DEPT_NO*) ◦ COURSE-OFFERING (OFFERING-NO, COURSE_NO*,INSTRUCTOR-NO*)  1:M between STUDENTS and REGISTRATION ◦ STUDENTS (STUDENT-NO) ◦ REGISTRATION (REGISTRATION-NO,STUDENT-NO* )  1:M between DEPARTMENTS and STUDENTS ◦ DEPARTMENTS (DEPT_NO) ◦ STUDENTS (STUDENT-NO, DEPT_NO*)  M:M between COURSE-OFFERING and REGISTRATION ◦ COURSE-OFFERING (OFFERING-NO, INSTRUCTOR-NO*) ◦ REGISTRATION (REGISTRATION-NO,STUDENT-NO* ) ◦ COURSE-SECTION-REGISTRATION (OFFERING-NO*, REGISTRATION-NO*, STATUS) 5/13/2019 20 5/13/2019 21 Database normalization  For implementation of the tables in a relational DBMS it is necessary to ensure that the database design is not flawed  The process of database normalization seeks to ensure that there are no significant data anomalies in a database design  A few design flaws may still remain in the tables that result from mechanical application of the conversion rules  A "normal form" simply means that a table conforms to certain rules aimed at testing the integrity of the table structure. 5/13/2019 22 Data anomalies   There are three kinds of data anomalies we are concerned about: ◦ An insert anomaly is the inability to insert or add data to a table because of a design problem ◦ A delete anomaly means that deleting certain data from a table inadvertently results in the loss of other related data which the user would like to retain ◦ An update anomaly simply means that a change in a data item must be performed in multiple locations rather than just one Increasing levels of normal forms indicate that the table conforms to stricter rules with consequently fewer data anomalies. 5/13/2019 23 Functional dependency (FD) In a table with fields X and Y, if there is only one possible value of Y for every value of X, then Y is said to be functionally dependent on X: field X determines the value in field Y Ina table with fields Student_Name Student_ID andand Student_Name, Student_ID, if there is only one possible value of Student_ID Student_Name for every value of Student_Name, Student_ID, thenthen Student_Name Student_ID is said to be functionally dependent on Student_Name: Student_ID: field fieldStudent_Name Student_ID determines determines thethe value value in field in field Student_name Student_ID  Why may FD work only in one direction? 5/13/2019 24 Motivating example  A department in a business school would like to store information in a database about professors, their offices, and courses taught by professors. The department wants to keep track of professors' teaching preferences in terms of the days and times they would like to teach a course. 5/13/2019 25 PROF-COURSES OFFICEPROF-NO 1001 NAME Dr. Benton NO 401A PHONE-NO 5-5515 1002 Dr. Carter 401B 5-8761 1003 Dr. Ross 402G 2-6785 1004 Dr. Green 401Z 5-9111 CREDIT- DAY- TIME- HOURS PREF PREF 3 MW 8 am Advanced AIS 1 MW 2 pm COMP101 Software tools 3 MWF 11 am ACCT209 Principles 3 TR 3 pm ACCT327 Intermediate I 3 MWF 10 am ACCT 328 Intermediate II 3 TR 10 am ACCT427 AIS 3 MW 10 am ACCT 209 Principles 3 TR 9 am COURSE-NO DESCRIPTION ACCT427 AIS ACCT 648 5/13/2019 26 First Normal Form (1NF) The PROF-COURSES relation above is an example of a nonnormalized relation since it contains a "repeating group."  The existence of multiple cell values in one or more non-key attributes for a single primary key value is called a repeating group.  A relation that is said to be in first normal form (1NF) must have exactly one value in each cell. That is, a 1NF rela tion ha s no repea ting g roups –(no multiple cell values in non-key attributes for single values in the primary key).  In a 1NF table, a ll non-key attributes should depend on the prim a ry key (but not necessa rily on the entire prim a ry key).  5/13/2019 27 PROFS (2NF) PROF-NO NAME OFFICE-NO PHONE-NO 1001 Dr. Benton 401A 5-5515 1002 Dr. Carter 401B 5-8761 1003 Dr. Ross 402G 2-6785 1004 Dr. Green 401Z 5-9111 COURSE-PREF(1NF) PROF-NO COURSE-NO DESCRIPTION CREDIT-HOURS DAY-PREF TIME-PREF 1001 ACCT427 AIS 3 MW 8 am 1001 ACCT648 Advanced AIS 3 MW 2 pm 1002 ACCT209 Principles 3 TR 3 pm 1003 ACCT327 Intermediate I 3 MWF 10 am 1003 ACCT328 Intermediate II 3 TR 10 am 1004 ACCT427 AIS 3 MW 10 am 1004 ACCT209 Principles 3 TR 9 am 1001 COMP101 Software tools 1 MW 11 am Open Access and create the above tables 5/13/2019 28 Insert Anomalies in 1NF  Add The course ◦ COURSE_NO - ACCT567 ◦ Description – ERP ◦ CREDIT-HOURS - 4  It is not possible to add course information without professor information because the primary key in the above table is a composite key of PROF-NO and COURSE-NO  This inability to add data to the database is called an insert anomaly 5/13/2019 29 PROFS (2NF) PROF-NO NAME OFFICE-NO PHONE-NO 1001 Dr. Benton 401A 5-5515 1002 Dr. Carter 401B 5-8761 1003 Dr. Ross 402G 2-6785 1004 Dr. Green 401Z 5-9111 COURSE-PREF(1NF) PROF-NO COURSE-NO DESCRIPTION CREDIT-HOURS DAY-PREF TIME-PREF 1001 ACCT427 AIS 3 MW 8 am 1001 ACCT648 Advanced AIS 3 MW 2 pm 1002 ACCT209 Principles 3 TR 3 pm 1003 ACCT327 Intermediate I 3 MWF 10 am 1003 ACCT328 Intermediate II 3 TR 10 am 1004 ACCT427 AIS 3 MW 10 am 1004 ACCT209 Principles 3 TR 9 am 1001 COMP101 Software tools 1 MW 11 am 5/13/2019 30 Update and Delete Anomalies in 1NF ◦ Please Change AIS credit hours from 3 to 4 ◦ If the description of a particular course is to be modified, the change will have to be performed multiple times rather than just once ◦ This duplication of the update effort is called an update anomaly and is also avoidable by redesigning the table ◦ Dr Ross has left the university please delete him from our records ◦ Because a particular course is taught by only one professor. If that professor leaves the university and his record is deleted from the database, we also inadvertently lose information about the course that was being taught by the professor (since that professor was the only one teaching the course). This inadvertent loss of data is called a delete anomaly 5/13/2019 31 Why did we have these anomalies?  The DAY-PREF and TIME-PREF fields are FD on the entire primary key, while the DESCRIPTION and CREDIT-HOURS are FD only on part of the key COURSE-PREF(1NF) PROF-NO COURSE-NO DESCRIPTION CREDITHOURS DAY-PREF TIME-PREF 1001 ACCT427 AIS 3 MW 8 am 1001 ACCT648 Advanced AIS 3 MW 2 pm 1002 ACCT209 Principles 3 TR 3 pm 1003 ACCT327 Intermediate I 3 MWF 10 am 1003 ACCT328 Intermediate II 3 TR 10 am 1004 ACCT427 AIS 3 MW 10 am 1004 ACCT209 Principles 3 TR 9 am 1001 COMP101 Software tools 1 MW 11 am 5/13/2019 32 Second Normal Form (2NF)  A relation is said to be in second normal form if ◦ (1) it is in 1NF, and ◦ (2) the non-key attributes in the relation are functiona lly dependent on the entire primary key.  The PROFS table is already in 2NF since every non-key field depends on the entire primary key (if there is only one field m a king up the prim a ry key, the ta ble will a utom a tically be in 2NF ) PROFS (2NF) PROF-NO NAME OFFICE-NO PHONE-NO 1001 Dr. Benton 401A 5-5515 1002 Dr. Carter 401B 5-8761 1003 Dr. Ross 402G 2-6785 1004 Dr. Green 401Z 5-9111 5/13/2019 33 Second Normal Form (2NF)  Then How do we decompose the COU RS E -P RE F Ta ble ?  The two relations into which the COURSE-PREF relation is decomposed are :COURSES which contains only course information, and PREFERENCES which contains only information about professor preferences for courses COURSE-PREF(1NF) PROF-NO COURSE-NO DESCRIPTION CREDITHOURS DAY-PREF TIME-PREF 1001 ACCT427 AIS 3 MW 8 am 1001 ACCT648 Advanced AIS 3 MW 2 pm 1002 ACCT209 Principles 3 TR 3 pm 1003 ACCT327 Intermediate I 3 MWF 10 am 1003 ACCT328 Intermediate II 3 TR 10 am 1004 ACCT427 AIS 3 MW 10 am 1004 ACCT209 Principles 3 TR 9 am 1001 COMP101 Software tools 1 MW 11 am 5/13/2019 34 COURSES (3NF) COURSE-NO DESCRIPTION CREDIT-HOURS ACCT427 AIS 3 ACCT648 Advanced AIS 3 ACCT209 Principles 3 ACCT327 Intermediate I 3 ACCT328 Intermediate II 3 COMP101 Software tools 1 COURSE-PREF (3NF) PROF-NO * COURSE-NO * DAY-PREF TIME-PREF 1001 ACCT427 MW 8 am 1001 ACCT648 MW 2 pm 1001 COMP101 MWF 11 am 1002 ACCT209 TR 3 pm 1003 ACCT327 MWF 10 am 1003 ACCT328 TR 10 am 1004 ACCT427 MW 10 am 1004 ACCT209 TR 9 am 5/13/2019 35 PROFS (2NF) PROF-NO NAME OFFICE-NO PHONE-NO 1001 Dr. Benton 401A 5-5515 1002 Dr. Carter 401B 5-8761 1003 Dr. Ross 402G 2-6785 1004 Dr. Green 401Z 5-9111 5/13/2019 36 Insert, update, and delete anomalies in 2NF Let us assume that there is exactly one phone in every office (PROFS table ).  PHONE-NO non-key attribute is functionally dependent on the OFFICE-NO non-key attribute.  Dependency between non-key attributes in a relation is called a transitive dependency.  PROFS (2NF) PROF-NO NAME OFFICE-NO PHONE-NO 1001 Dr. Benton 401A 5-5515 1002 Dr. Carter 401B 5-8761 1003 Dr. Ross 402G 2-6785 1004 Dr. Green 401Z 5-9111 5/13/2019 37 Insert, update, and delete anomalies in 2NF ◦ Add Office # 403B and phone number 55519 ◦ It is not possible to add information about a new office and phone number until a professor is assigned to that office ◦ Dr Green has left the school please delete him from our records ◦ When a professor record is deleted (upon the professor leaving the university), we also inadvertently lose office and phone number information for the office that was occupied by the professor 5/13/2019 38 Third Normal Form (3NF)  A relation is said to be in third normal form if ◦ (1) it is in 2NF, and ◦ (2) all functional dependencies in the relation originate from the primary key ◦ (3) the relation contains no derived fields  The PROFS relation is decomposed into two relations: PROF-INFO (3NF) PROF-NO NAME OFFICE-NO* 1001 Dr. Benton 401A 1002 Dr. Carter 401B 1003 Dr. Ross 402G 1004 Dr. Green 401Z OFFICES (3NF) OFFICE-NO PHONE-NO 401A 5-5515 401B 5-8761 402G 2-6785 401Z 5-9111 5/13/2019 39 Normalization summary Rela tion a re: 1. in 1NF if there are no repeating groups in the relation (every cell contains a single value; exactly one non-key attribute value for every primary key value), 2. in 2NF if every non-key field is functionally dependent on the entire primary key (no field depends on only part of a composite primary key) 3. in 3NF if all functional dependencies in the relation originate from the primary key (no transitive dependencies) and the relation contains no derived fields 5/13/2019 40 Sales Order Fiction Company 202 N. Main Mahattan, KS 66502 CustomerNumber: Customer Name: Customer Address: Item Ordered 800 801 805 1001 ABC Company 100 Points Manhattan, KS 66502 Sales Order Number: 405 Sales Order Date: 2/1/2000 Clerk Number: 210 Clerk Name: Martin Lawrence Description widgit small tingimajigger thingibob Quantity 40 20 10 Unit Price 60.00 20.00 100.00 Total 2,400.00 400.00 1,000.00 3,800.00 Order Total (SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo, ClerkName, ItemNo, Description, Qty, UnitPrice,Total) 5/13/2019 41 Normalization: First Normal Form      Separate Repeating Groups into New Tables. Repea ting Groups Fields that may be repeated several times for one document/entity Create a new table containing the repeating data The primary key of the new table (repeating group) is always a composite key; Usually document number and a field uniquely describing the repeating line, like an item number. The new table is as follows: ◦ (S a lesOrderNo*, Item No, Description, Qty, U nitP rice) ◦ The repeating fields will be removed from the original data table, leaving the following. ◦ (S a lesOrderNo, Da te, Custom erNo, Custom erNam e, Custom erA dd, ClerkNo, ClerkNa m e) ◦ These two tables are a database in first normal form 5/13/2019 42 Normalization: Second Normal Form Remove Partial Dependencies.  Functiona l Dependency The value of one attribute in a table is determined entirely by the value of another.  Pa rtia l Dependency A type of functional dependency where an attribute is functionally dependent on only part of the primary key (primary key must be a composite key)  Create separate table with the functionally dependent data and the part of the key on which it depends  5/13/2019 43 Normalization: Second Normal Form   The new table will contain the following fields: ◦ (Item No, Description) All of these fields except the primary key will be removed from the original table. The primary key will be left in the original table to allow linking of data: ◦ (S a lesOrderNo*, Item No*, Qty, U nitP rice)  Never trea t price a s dependent on item. Price ma y be different for different sa les orders (discounts, specia l customers, etc.)  Along with the unchanged table below, these tables make up a database in second normal form: ◦ (S a lesOrderNo, Da te, Custom erNo, Custom erNam e, Custom erA dd, ClerkNo, ClerkNa m e) 5/13/2019 44 Normalization: Third Normal Form    Remove transitive dependencies. Tra nsitive Dependency A type of functional dependency where an attribute is functionally dependent on an attribute other than the primary key Thus its value is only indirectly determined by the primary key Create a separate table containing the attribute and the fields that are functionally dependent on it. Tables created at this step will usually contain descriptions of either resources or agents. Keep a copy of the key attribute in the original file 5/13/2019 45 Normalization: Third Normal Form    The new tables would be: ◦ (Custom erNo, Custom erNam e, Custom erA dd) ◦ (ClerkNo, ClerkNa m e) All of these fields except the primary key will be removed from the original table. The primary key will be left in the original table to allow linking of data as follows: ◦ (S a lesOrderNo, Da te, Custom erNo*, ClerkNo*) Together with the unchanged tables below, these tables make up the database in third normal form. ◦ (Item No, Description) ◦ (S a lesOrderNo*, Item No*, Qty, U nitP rice) 5/13/2019 46 City Public Library 5/13/2019 47 We therefore have the following sets of relationships    1) 1:M between MEMBERS and CHECK-OUT, (2) a 1:M between CLERKS and CHECK-OUT, (3) a 1:M between CLERKS and CHECK-IN, (4) a 1:M between MEMBERS and CHECK-IN, (5) a 1:M between CASH and CHECK-OUT, (6) a M:M between BOOKS and CHECK-OUT, (7) a M:M between BOOKS and CHECK-IN, and (8) a M:M between CHECK-IN and CHECK-OUT. For all 1:M relationships, the conversion rules require that the primary key of the table on the "one" side of the relationship be posted as a foreign key to the table on the "many" side of the relationship. For the M:M relationship between COURSE-OFFERING and REGISTRATION, we must create a new composite key table. 5/13/2019 48 Tables          BOOKS (BOOK-NO, TITLE, AUTHOR, PUBLISHER, ISBN, TIMES-CHECKEDOUT) CASH (ACCOUNT-NO, BANK, ACCT-NO-AT-BANK, TYPE, DATE-OPENED, CURRENT-BALANCE) MEMBERS (MEMBER-NO, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, DATEOPENED, DATE-CLOSED, FINES-DUE) CLERKS (CLERK-NO, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, SALARY, DATE-HIRED, DATE-LEFT) CHECK-OUT (CHECK-OUT-NO, DATE, DUE-DATE, MEMBER-NO*, CLERK-NO*, ACCOUNT-NO*) BOOKS-CHECKED-OUT (CHECK-OUT-NO*, BOOK-NO*) CHECK-IN (CHECK-IN-NO, DATE, CLERK-NO*, MEMBER-NO*) BOOKS-CHECKED-IN (CHECK-IN-NO*, BOOK-NO*, FINE) CHECKIN-CHECKOUT (CHECK-IN-NO*, CHECK-OUT-NO*) 5/13/2019 49 5/13/2019 50 Summary    Physical design involves translating a logical model of a database system to constructs that can be implemented in a database system, i.e., tables The rules for converting REA diagrams to tables were covered. Basic rules for mandatory relationships were first presented, followed by the special case rules for dealing with optional relationships. The process of database normalization was then discussed. The anomalies that can result from nonconformance to normal forms were described. First, second, and third normal form were explained. 5/13/2019 51 Kim’s Flowers Purchasing Cycle Assignment Required: • Based on the narrative below, your team should follow steps 1-6 of the event oriented modeling (covered in chapter 8) for constructing a comprehensive EER diagram. You should submit a typed document describing in detail each one of the steps. • Your team would submit a complete EER diagram and Level 0 DFD for the chosen enterprise. The diagrams should be prepared in Microsoft Word or PowerPoint. • Corrections and comments from the first stage should be implemented in subsequent stages. • If you identify more resources/events/agents then are needed NO points will be deducted! • All students are required to equally participate in the project and may be called upon to present any part of the project. Kim Burke is the owner and operator of a small flower shop, Kim's Flowers. She has decided to develop a database system to track her purchases, floral inventory, accounts payable and cash payments. However, she does not have time to do the development herself. Therefore, she has hired you to design and implement the system for her. She describes the requirements of her system as follows: Whenever flowers are needed, a purchasing agent enters a purchase order for the items needed directly into the computer system. To accommodate all of her floral needs, the purchasing agent may need to prepare several orders, as Kim orders flowers from many different vendors. The purchasing agent may order several types of flowers from a single vendor on one order. Kim approves and prints these purchase orders and sends them to the vendors. A floral designer records the receipt of flowers from a vendor on a special screen in her accounting system. At the end of each month, an accounts payable clerk creates checks to pay for all amounts charged through the end of the month. Once the checks are created, Kim authorizes the checks, prints them and mails them to vendors. Kim also uses special screens in the computer system to perform maintenance for inventory items (add, modify and delete items from the file), and to record disposal of old or damaged flowers. • • Prepare an REA diagram describing Kim's database. You may repeat a box for an individual entity if necessary to make your drawing neat. Kim would like for you to consider that, eventually, as new stores are opened, she will have store managers in each store that will perform the tasks she now performs. As a result, the system should be planned to always store information about the employee associated with transactions, whether that employee enters information, performs a task (such as delivery) or formally authorizes the transaction. Hints: (1) Each item on a floral receipt may be from a different purchase order. Different items on a single purchase order may be received at different times using different receipts. (2) Each check may pay for several receipts. However, a single receipt will always be paid for using the same check. (3) Multiple floral items may be disposed of using a single form. Prepare the context diagram and the level 0 DFD.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello, see the attached document below

Running head: KIM’S FLOWERS

1

Kim’s Flowers Expenditure Cycle
Name
Institution

KIM’S FLOWERS

2
Kim’s Flowers Expenditure Cycle

The Kim’s Flowers Expenditure Cycle database from 1NF t...


Anonymous
This is great! Exactly what I wanted.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags