CIS 110 – Introduction to Computer and Information SciencesDatabase Lab Using Microsoft Access – 2 Table DatabaseObjectives: You will create a small Inventory database for a video store selling DVDs, CDs, and VHS tapes. This database will contain two tables to apply the concepts of records, fields and keys, use list/table view vs. a form, and design and use simple sorts, queries, and reports.At the end of the lab you may copy your database to a floppy disk to save. However, we will first create the database and work with it on the hard drive as the floppy disk is too slow.1. On the Windows Desktop, double-click on the icon for Microsoft Access(the database in MS Office). If you don’t see it, launch from the StartMenu.2. Once Access has started you will see the Task Pane at the right of the screen with different options for creating a new database. Click Create a new file and then select Blank Database. A dialog box opens asking where to save the new database. Create the new file on your floppy drive. Change the File Name to Lab3_Access. The main Database Window opens which shows all the objects you can work with in the database. When you click on a type of object in the left pane you will see the existing objects in the right pane, along with some options for creating new ones.3. Double-click “Create Table in Design View”. If you don’t see the above choices, click on Tables at the left to display them. Page 1 of 106/30/044. You should now be in Design View which allows us to set & edit the properties of thetable (the record & field definitions) rather than viewing the actual data stored in it. The upper pane of Design View shows the name of all the fields in the table together with their Data Type. The type affects how that field is stored on disk, how it can be displayed, and what kind of calculations might be performed on it. Since this is a newtable this region is empty.Click in the first cell under Field Name andtype the name “ARTIST_ID”. Don’t type thequotes!! Next, click in the Data Type cell tothe right. A small down-arrow button shouldappear. Click on it and choose AutoNumber from the list.5. Since the ARTIST_ID code should uniquelyidentify each product we would like the database toprevent duplicates. Identify this field as a Key byright-clicking on the black selection arrow to theleft of “ARTIST_ID”. Choose Primary Key fromthe pop-up menu that appears. A small key icon will appear next to ARTIST_IDconfirming the change. This is only done for ONE field!6. Add the additional fields:Field Name Data Type PropertiesArtist_FName Text Field Size – 15Artist_LName Text Field Size – 20 RequiredArtist_DOB Date/Time Format – Short DateOscar_Winner Yes/No Default Value – NOIn the lower part of the Design View you can fine tune the field properties. The gray square at the lower right will explain each property. 7. Save the new table by clicking on the Save icon in the main toolbar . Youwill be prompted for a name – call it “Artist” and click OK. Close Design View by clicking on the X.Page 2 of 106/30/048. You should see the Database window again. Double-click on your table Artist to open it what Access calls Datasheet View for data entry or editing, similar to a spreadsheet grid. Each row contains a record – the information about a single member. Each column contains a field – a characteristic, a single fact, about a member.Click in each cell to add data for each field describing an Artist:Artist_ID Artist_FnameArtist_Lname Artist_DOB Oscar_WinnerAutoNumber (1) Peter Jackson 10/31/61 NoAutoNumber (2) James Cameron 8/16/54 YesAutoNumber (3) Gus VanSant 7/24/52 NoAutoNumber (4) Your Name Your NameYou can move from field to field using the cursor keys, the tab key, or the mouse. 9. From the File menu, select the Print Option and print the contents of the table.10. Close Design View by clicking on the X.11. Using the same steps create a second table named INVENTORY with the following fields:Field Name Data Type PropertiesUPC Text Field Size – 12 Primary KeyTitle Text Field Size – 50 RequiredArtist_ID Look Up Wizard(see instructions below)Format Look Up Wizard-(see instructions on next page)Quantity Number Field Size – Long IntegerArtist_ID Lookup Wizard:We want a relationship to be created between the Artist_ID in the Artist Table and theArtist_ID in the Inventory table. For this, we will create a lookup wizard for the Artist_ID so that it will limit the available values to those artists that currently exist inthe Artist Table.a) Select Data Type “LookupWizard”b) Select “I want the look up column to look up the values in a table or query”, Hit the Next buttonPage 3 of 106/30/04c) Select “Artist” as the table to provide the values for your look up column, Hit the Next buttond) Select (Double Click) Artist_ID and Artist_LName as the fields that contain the values you want included in your look up column, Hit the Next buttone) Select ARTIST_ID as the field to sort by in ascending order. Hit the Next button.f) Click to deactivate “Hide Key Column” to display both fields in your look up column, Hit the Next buttong) Select Artist_ID as the value to store in the table row, Hit the Next buttonh) Label your lookup column as Artist_ID, Hit the Finish Buttoni) Save your table as “INVENTORY” to create the relationshipFormat Loopkup Wizard:Since we want Format to only have as possible values CD, DVD, or VHS, go back and change its Data Type to “Lookup Wizard”. A dialog box will open.In the Wizard, select “I will type in the values I want” and click “Next”.In the grid that appears, enter “CD”, “DVD” and “VHS” (NO QUOTES!) Click “Next”. In the last dialog click “Finish”.12. Save the new table by clicking on the Save icon in the main toolbar and close the design view. You should see the Database window again. Double-click on your table Inventory toopen Datasheet View. Click in each cell to add data for each field describing a DVD:UPC Title Artist_ID Format Quantity012345678901 Return of the King 1 DVD 300You can move from field to field using the cursor keys, the tab key, or the mouse. 13. Although Table View (List View according to Beekman text) is good for quickly scanning records – once you’ve adjusted the column widths – it is not very good for data entry or browsing one record at a time. You will create a Form for this