hello i need help with this assignment very urgent

ACTG 378 Lab 2 Part 1 Name: _______________________ Relational Databases Section : _______ Cover Sheet – You Must turn in this Sheet with your Lab printouts ! Lab 2 Part 1 requirements will be printed. I will grade your lab using the requirements checklist below. Requirements : Required documents must be attached to this requirements sheet in the order listed! This requirements page must be stapled to the front of the documents you are handing in! Datasheet View Printouts Requirement Customer table Datasheet View printed on a single, portrait page? Does Customer table Datasheet View printout contain only Michael and Camille as customers? Sale -Inventory table Datasheet View printed on a single, portrait page? Is all information complete and correct for SALE -1000 and SALE -1001? Documenter Printouts Requirement Customer table properties printed? Are the required items in the Customer table properties report circled? Relationship Window Printout Requirement Relationship Window printed on a single, landscape page? Do all table names include YourLastName? Are correct relationships established? Do all relationships display the correct cardinalities, i.e. is referential integrity enforced? Purpose This lab demonstrates the procedures used to create a relational database from an entity - relationship (ER) diagram and a data dictionary. This tutorial uses REA (Resources, Events, Agents) diagrams and references using an REA diagram to crea te tables. Please read the assigned chapters in the text and examine your class notes to obtain an overview of REA modeling and implementation in a relational database. We will cover REA modeling during lecture . Knowledge Objectives This lab provides an exposure to the following areas: Create Relational Database and Database Tables Create a new relational database. Create new database tables. Format database table fields. Provide data input controls by including data input restrictions. Establish Relationships Establish relationships between tables. Enforce referential integrity. Document Database Information Document table definitions and the relationship window Vocabulary Table A table is a collection of data about a specific topic, such as inventory or sales. Early database programs could handle only a single table. Field A column entry in a table of data. Each piece of information in a table is stored in a predefined area called a field. For example, an inventory table contains fields that describe the product and list the price of the product. Record A row entry in a table of d ata. Each record is identified with a unique identifier or number. Relational Database A type of database that allows multiple, related tables to be established within the same database.

This type of database is termed “Relational” because of the relationships established between tables. Merchant of Venice Sale Event Case With help from a private consulting firm, the Me rchant of Venice developed the REA diagram below for her sale event. As denoted in the diagram, sales involve both a customer buying silk and a Merchant of Venice employee selling the silk. Because sales decrease inventory, a relationship between sales and inventory is also depicted. The Merchant of Venice Sale Event Once the Merchant completed the REA diagram, she created a data dictionary that identifies the tables and fields required to store sales information. Figure 1 contains the Merchant’s data dictionary, and Figure 2 contains sample data. This lab uses the da ta dictionary to guide the database creation process. Because the data dictionary is such a critical input to the database creation process, take a few moments to study the data dictionary and reconcile the data dictionary to the above REA diagram. A fe w of the important items you should notice include: 1) The Merchant used efficient relationships when she created the tables listed in the data dictionary. a) In an efficient database implementation, a table is made for each entity modeled. As shown in F igure 1, tables were made for the Customer, Employee, Inventory, and Sale entities. b) Efficient implementations also require a n extra table to be created for each many -to-many relationship. The Sale -Inventory table included in the data dictionary is an example of a many -to-many or relationship table. Note how many -to-many relationship tables are named by hyphenating the names of the two related tables. 2) The Sale table contains the Employee # and Customer # foreign key fields. These fields were included in the Sale table so the one -to-many relationships can be implemented. As shown in the data dictionary, the key fields from entity on the one side of the relationships have been included as foreign k ey fields in the entity on the many side of the relation ships. 3) Each of the entity tables contains a key field that serves as a unique identifier for each record entered into the table. 4) The Sale -Inventory relationship table uses two key fields. These two key fields correspond to the key fields of the entity tables the relationship table connects. The use of two or more key fields is called a composite key. 5) The Merchant has identified how she wants each of the fields stored, e.g. text, time/date, number. Inventory Customer Sale Employee 6) The Merchant has included additional formatting information for many of the fields. Structured Database Creation Using a structured approach to creating relational databases greatly improves the database implementation process. The Merchant is using the following process to implement her database: 1) A model of the database was created. A full discussion of ER and REA modeling is beyond the scope of this lab . However, note how the starting point for the database created in this tutorial was an REA diagram. 2) Based on the REA diagram, the required fields and tables are identified and listed in a paper database or data d ictionary. The data dictionary in Figure 1 fulfills this requirement. 3) A new relational database will be created. 4) Tables are designed within the database. 5) Relationships between the tables are established. 6) Data is entered into the database. Creat e New Relational Database With the data dictionary to use as a roadmap, we are ready to create a relational database that stores sales information for the Merchant of Venice. To create the relational database: 1) Start Microsoft Access. 2) Create a n ew Blank Desktop Database. 3) Name the new database “ your lastname_lab1” (Make sure you know where the file is saved) The new database opens to the Database Window, which is depicted in Figure 3. The Database Window serves as the main navigational tool for Access databases, as the Database Window organizes and lists all of the objects in the database. Access structures data so all the components related to a database are stored within a single file. These components are referred to as objects and includ e: Tables, which are used to define and store information. This lab focuses on tables. Queries, which are used to extract and process information stored in the database. Two subsequent labs provide practice with queries. Forms, which provide interfaces for both entering and viewing information. Forms are also presented in a subsequent lab. Reports, which are used to print information from databases. Macros, which are used to automate repeated actions. Modules, which are used to store Visual Basic programs. Create Tables With the relational database created, tables can be designed to store data. Before a table can store data, we need to tel l Access what data will be stored in the table and we need to tell Access how to store the data. This section walks through the process of designing the Customer table. Once the Customer table is designed, you will have the opportunity to design the remain ing tables. Customer Table The Customer table is used to store information about each of the Merchant’s customers. The data dictionary in Figure 1 provides the details required to correctly design the Customer table. To design the Customer table: 1) Click Create, then Table to create a new table. 2) To start designing a new table, click View, then Design View. 3) Save the table as “Customer_yourlastname” 4) The Design View Window appears. Figure 4 contains a picture of the Design View Window. This win dow is used to define the fields that make up the table. From Figure 4, note how the upper portion of the window is called the Field Entry area while the lower portion of the window is called the Field Properties area. The following sections provide guidan ce for creating and formatting each of the fields in the Customer table. Customer # To define a field that stores the customer number: 1) In the Field Entry area, click on the top cell in the Field Name column. 2) Enter Customer # into this field and pr ess tab. 3) In the Data Type field, select short text. Why is the customer number stored as text when it is called a number? To distinguish between different identifying numbers, the Merchant plans to use text prefixes for all key field entries. For example , customer numbers will contain a CUST - prefix that produces customer numbers like CUST -01 and CUST -02. 4) The data dictionary directs us to include a description for the Customer # field. Tab over to the description column and enter the following descripti on: “All customer numbers contain a CUST - prefix” 5) The data dictionary also identifies the Customer # field as the Primary Key. To make the Customer # field the Primary Key Field: a) Use the mouse to click on the gray box to the left of the Customer # t ext you entered. 6) Clicking on the gray box selects the row . With the Customer # row highlighted, click the Primary Key button. A small key will appear in the gray box to the left of the Customer # text, which indicates the Primary Key Field status. Click on the next empty row so you are ready to create the next field. Name The data dictionary identifies the Name field as a required field, which means a name must be entered for any Customer # entered into the table. When fields are not formatted as required, data entry clerks might leave the fields blank. Requiring an entry in the Name field is a good data entry control. It forces the sales clerks to record customers’ names. To create the Name field and identify it as a required field: 1) Create the Name field the same way you created the Customer # field. a) Do not make the Name field a primary key field. As denoted in the data dictionary, only the Customer # is a key field. 2) The Name field is formatted as a required field by changing the Required text box to read Yes. The Required text box is on the Genera l tab in the Field Properties area. Use the arrow on the right to choose a valid option. Street Address and City The Street Address and City fields are similar to the Customer # field created earlier. Enter the definitions for these fields. Zip Code The Merchant has included both formatting and validation rule instructions for the Postal Code field. To create the Zip Code field: 1) Enter the Field Name and designate the Data Type as Number. 2) Since this is the first field that uses a number Data Type, we need to spend a few moments examining how Access works with numbers. a) Click on the Zip Code field you just created. b) Click on the Field Size text box, which appears on the General tab in the Field Properties portion of the window. c) Larger numbers or numbers that use decimal places require more storage space. Also notice that Single and Double formats are the only formats that support decimal places. Since all dollar values normally contain decimal places, you must always use either Single or Double formats for dollar values. When you enter the number formats specified in the data dictionary, try to understand the logic behind the number formats specified by the Merchant. d) Use the pull -down menu on the Field Size text box to change the Field Size format to Long Integer. 3) For zip codes to look right when they are printed, they must contain leading z eros. For example the 00 012 postal code would not be recognized as a valid code if it were displayed without the leading zeros as 12 . To format the Postal Code field to include leading zeros: a) Select the Postal Code field. b) In the Format text box, which is on the General tab in the Field Properties po rtion of the window, enter 0000 0. Entering the five zeros instructs Access to display five digits before the decimal in this field. 4) The data dictionary also lists a validation rule for the Zip Code field of >=0 and <=9999 9. This validation rule ensures that the postal codes entered into this fie ld are within a valid range. Since zip codes cannot be negative numbers, we know postal codes must be greater than or equal to 0. We also know that zip co des are five digits. Since 999 99 is the largest possible five -digit number, we know the postal codes mus t be less than or equal to 99 999 . To create the validation rule for the Postal Code field: a) Select the Postal Code field in the Field Entry portion of the window. b) Enter the >=0 and <=999 99 validation rule into the Validation Rule text box foun d on the General tab. AR Balance Creating the AR Balance field introduces only one new topic; formatting how numbers are displayed. To create and format the AR Balance field: 1) Enter the Field Name, Data Type, and description. 2) Change the Field Siz e text box to Single. 3) Format how the field contents are displayed by changing the Format text box on the General tab to read Currency. 4) Change the Decimal Places text box on the General tab to read 2 so the AR balance always displayed with two decimal places. Save Customer Table With all of the fields defined and formatted, we are ready to save the Customer table. To save a table: 1) Click on the close window X to close the customer table. 2) When prompted, select Yes to save the table. 3) If you have not already names the table, e nter the name of the table in the Save As pop -up window. Name the table as “Customer_ YourLastName”. Navigating the different Table Windows Once the Customer table is saved, Access returns to the Database Window. To open the Customer table in Design View, which is the view that allows you to change the structure of the table: 1) Double Click on the Cu stomer table that now appears in the Database Window. 2) Click View, Design View. Access takes you to the Design View, which is the same window we used to create the table. To view the contents of a table, switch to Datasheet View. Enter Trial Data Before moving on to creating the remaining tables, take a few moments to try entering a few trial records in the Customer table. 1) Open the Customer table in Datasheet View 2) To enter the first trial record: a) Type CUST -01 into the Customer field. b) Earlier, we told Access that the Name field required an entry. To test Access, leave the Name Field Blank and proceed to the Street Address field. c) Enter your street address in the Street Address field. 3) In the Pos tal Code field, try to enter the following codes: -23, 123456, and Pickle. Notice how the restrictions we placed on this field do not allow incorrect codes to be entered. Enter the valid postal code of 0 012. Notice how Access displays the leading zeros. 4) Enter an AR Balance for CUST -01. 5) Once you have entered valid postal code, Access returns a message stating that the Name field is a required field. Go back and enter your name in the Name Field. 6) Finally, Access accepts your first record. 7) To enter the second trial record: a) Type CUST -01 into the Customer field. b) Make up information for the remaining fields. c) Once you have entered all of the information, Access gives you an error that the primary key field cannot contain duplicate entries. Correct the error by changing the second customer number to CUST -02. 8) Resize the field widths so you can see all the information in each field. Field sizes are adjusted by dragging the black lines that appear between the field names, much like resizing columns in Excel. 9) These two records were to demonstrate data input and the effects of the table design. Delete the two records from your Customer Table. Create the Remaining Tables Use the data dictionary and the above narrative to create the remaining Employee, Inventory, Sale, and Sale -Inventory tables. When naming your tables, be sure to use the same “TableName_ YourLastName” format when naming your remaining tables. The only n ew skill required for this section is the ability to designate two key fields within the same table, as is required for the Sale -Inventory table. The Sale -Inventory table is used to implement the many -to-many relationship between the Sale and Inventory ta bles and requires two key fields. To designate two key fields with the same table: 1) Select both of the key fields. 2) Click the Primary Key Button. Keys will appear to the left of both of the columns. Establish Relationships Although you have cre ated five tables in your database, Access has not been told how the tables are related. Until relationships are created between the tables, Access treats each table as being completely unrelated to the other tables. Relationships between tables must be ma nually defined. To establish the relationships: 1) Click on Database Tools and then select Relationships. 2) From the Show Table pop -up window add all five tables to the Relationship Window. 3) Resize the table boxes so all of the fields are visible, the compl ete field names are visible, and the full table names are visible. 4) Arrange the tables in the same order as the REA diagram, as depicted on page 2 of this handout (note: you may have created additional tables due to many -many relationships). The lines de picted in the figure represent the relationships you must enter. 5) Relationships need to be established between related fields. For example, a relationship between the Employee # field in the Employee table and the Employee # in the Sale table needs to be established. To establish this relationship: a. Click on the Employee # field in the Employee table and hold down the left mouse button. b. Drag your cursor over the Employee # in the Sale table and release the mouse button. c. A Relationships pop -up window appears. The window provides information about the two fields to be related. Ensure you are relating an Employee # field to an Employee # field. Access does not require related fields to have the same name. For example, we co uld have named the employee number field in the Sale table Salesperson #, which would have been related to the Employee # field in the Employee table. In fact, using names like Salesperson # in the Sale table is a good idea, as it better specifies the emp loyee number to be entered into the field. d. Click on the Enforce Referential Integrity option box. What does referential integrity mean? When you enforce referential integrity, you restrict Access to only accept values in the n side of the relationship t hat already exist in the 1 side of the relationship. For example, enforcing referential integrity between the two Employee # fields, tells Access not to accept an employee number in the Sale table that has not been defined in the Employee table. Enforcing referential integrity is a strong control, as it greatly limits the amount of data entry errors. When you enforce referential integrity, Access labels relationships with 1 and ∞ symbols. If you do not enforce referential integrity, Access does not label the relationships. e. Select Create and Access returns to the Relationship Window. 6) Establish the remaining relationships. Data Entry Once the tables have been defined and r elationships have been established, the database is ready to begin storing information. The only trick to data entry is to remember the restrictions created when referential integrity is enforced. For example, data must be entered into the Customer and E mployee tables before a record can be entered into the Sale table because the customer numbers and employee numbers entered in the Sale table must already exist in the Employee and Customer tables. 1) Enter the information from Figure 2 into the database. None of the records will contain blank fields. Additional Hints: a. Sale_Inv Table Sale Amount - Typically in a transaction file the amount fields are for "each" product, i.e. the two emeralds are sold for $60 each - $60, not $120, is the sale price ent ered. A computer application is very good at extending quantity times price, so storage of the data is at the detail level (i.e., per unit) -- not the summary level. b. Inventory Table - Qty field: The table definition for this field does not specify chang ing the "number of decimals" from the default setting, " auto". The result is that your reports display 2 decimals. This is fine. If you were to sell 1/2 a yard of silk, your database could handle this transaction. Print Database Information Once a database is implemented and contains data, three basic items can be printed. First, the contents displayed in the Datasheet View can be printed. Second, table configuration and formatting information can be printed. Third, the Relationship Window can be printed. Print Datasheet View To print the data entered into a table, you can simply make a printout from the Datasheet View of a table. Required : Make a printout of the Customer table Datasheet View and make a printout of the Sale -Inventor y table Datasheet view. The printout of the Sale -Inventory table Datasheet View should fit on a single portrait page. Both of these printouts are requirements for this lab. Print Propertie s Access can also print information about a table’s design a nd formatting. 1) Hint: you will need to use an option on the Database Tools menu called the Database Documenter. 2) Required : Print the properties of the Customer table. a. Close all tables, queries, forms, etc. before you attempt to print the properties of any object. Access is known to provide strange error messages and refuse to print properties when objects are open. b. Required : Before you turn in the table property repor t you must circle the following items on the report: i. The >=0 and <=9999 9 validation rule for the zip code. ii. The section that formats the AR Balance field as Currency. Print the Relationship Window Required : Making a printout of the Relationship Window provides a quick overview of a database’s structure. 1) Click on the Relationships in the Database Tools Tab. 2) Click on Relationship Report at the top of the Window. 3) Print the report. Relationship Window pr intouts are very useful to show others the contents of your database. Conclusion Congratulations, you have now completed Part 1 of Lab 1 ip. You should now be more comfortable with creating relational databases. You should also recognize the importance of having a correct REA diagram before you begin to create a database. You can now complete Part 2 of Lab 2. Figure 1 – Data Dictionary Cust omer Field Name Data Type Additional Requirements Customer # Text Primary Key “All customer numbers contain a CUST - prefix” Description Name Text Required field Street Address Text City Text Zip Code Number Long Integer Field Size 0000 0 Format > =0 And <=99 999 Validation Rule AR Balance Number Single Field Size Currency Format Display 2 decimal places “Customer Accounts Receivable Balance” Description Employee Field Name Data Type Additional Requirements Employee # Text Primary Key “All employee numbers contain an EMP - prefix” Description Name Text Required field Street Address Text Required field City Text Required field Zip Code Number Long Integer Field Size 0000 0 Format >=0 And <=9 9999 Validation Rule YTD Salary Number Single Field Size Currency Format Display 2 decimal places “Year To Date Gross Salary” Description Inventory Field Name Data Type Additional Requirements Item # Text Primary Key “All item numbers contain an ITEM - prefix” Description Description Text “Item Description” Description Required field Quantity on Hand Number Single Field Size Format Standard Standard Unit Cost Number Single Field Size Format Currency List Price Number Single Field Size Format Currency Unit of Measure Text Create Lookup list containing Bolt, Yard, Each Restrict input to Lookup list Figure 1 – Data Dictionary (cont.) Sale Field Name Data Type Additional Requirements Sale # Text Primary Key “All sale numbers contain a SALE - prefix” Description Customer # Text Required field Employee # Text Required field Date Date/Time Format Medium Date Total Sale Amount Number Single Field Size Format Currency Display 2 decimal places Sale -Inventory Field Name Data Type Additional Requirements Sale # Text Primary Key Item # Text Primary Key Quantity Number Single Field Size Format Standard Actual Sale Price Number Single Field Size Format Currency Display 2 decimal places Figure 2 – Initial Data Along with creating the relational database, the Merchant requested you to enter the following information into the database. Products available for sale Merchant of Venice Employees Merchant of Venice Customers The Merchant also requested the following sales be recorded in the database. Please note that the customer accounts receivable balances already reflect these sales. August 5, 2015 Michael purchased 1 bolt of bolt of the bulk blue silk for $170, 2 emer alds for $60 each, and 1 yard of the fine quality orange silk for $6.00 from the Merchant on SALE -1000. August 6, 2015 Camille purchased 1 emerald for $80.00 and 1 yard of the fine quality orange silk for $12.00 from Henry on SALE -1001 Employee # Name Street Address City Postal Code YTD Salary EMP -1 Merchant 115 Canal St Fargo 55870 $0.00 EMP -2 Henry 116 Canal St Tucson 58528 $1,000.10 Customer # Name Street Address City Postal Code AR Balance CUST -45 Camille 12 Venice Way Anchorage 57454 $500.50 CUST -46 Michael 100 Ponderosa Way Moorhead 51221 $9,000.10 Item # Description Quantity on Hand Standard Unit Cost List Price Unit of Measure ITEM-100 Blue Silk (Bulk) 100 $100.00 $180.00 Bolt ITEM-200 1.0 Carat Emeralds 15 $20.00 $80.00 Each ITEM-300 Orange Silk (Fine Quality) 12 $8.00 $12.00 Yard Figure 3 – Database Window Figure 4 – Customer Table in Design View