Assessment D.pdf

Assessment D In this unit I am going to create a database to store a given set of data and extract useful information. The dataset I have been given is called tickets and is in a CVS file format.

As you can see this data is difficult to interpret. As a result of this imported it into Microsoft Excel so it was easier to read and analyze.

The next step was to look at the row headings and decide how I can split them up into different tables. A table stores information on an object. These are the row headings from the dataset Adam Bullock – 8035 19319

Page |1

           

Order Number Temp Title First Name Last Name Address Line 1 Town County Post code Card Type Card Number Expiry Date Start Date

I have created a data dictionary to help me sort out which attributes/field names go with which table/entity. I have also defined the type of data, field length and validation(s). Entity Name

Performance

Attribute

Order Number temp Performance Name

Performance Date Performance Type

Data Type

Field Length

Validation

Description

Number

5

Text field

23

Required? Lookup wizard

Primary key No "Blood Brothers";"Chicago";"Chitty Chitty Bang Bang";"Evita";"Guys and Dolls";"Jack and the Beanstalk";"Les Miserables";"Mamma Mia";"Phantom of the Opera";"Sound of Music";"South Pacific";"We will Rock You" Yes Yes

Date/Time

16

Text

1

Number of Tickets

Text

10

End Date Cost of Seats

Date/Time Text

16 6

Adam Bullock – 8035 19319

Required? Required? Lookup wizard Required? Validation rule Required? Required? Required?

"E";"M" Yes <7 Yes No No

Page |2

Customer

Order Number temp Title

First Name Last Name Address Line 1 Town County Post Code

Card Type Card Number Expiry Date Start Date

Number

5

Text Field

4

Text Field Text Field Text Field Text Field Text Field Text Field

Text Field Text Field Date/Time Date/Time

25 25 25 16 16 7

16 12 16 16

Required? Lookup wizard Required? Required? Required? Required? Required? Required? Input mask Validation Text Required? Required? Required? Required? Required?

Foreign key No "Mrs";"Miss";"Mr";"Ms" Yes Yes Yes Yes No Yes LL09#0LL Incorrect Post Code Yes Yes Yes Yes No

I have created this data dictionary so that if i was going to get someone else to create my database for me they would be able to do the entity tables with only this one sheet. Below I have explained what some of the above terminology is. o Data types - are there to help the database programme understand what types of it is dealing with. o Field length – is there to limit the number of characters that the user can enter into the data field. o Validation – is there to prevent the wrong data from being entered.  Validation type; Required – this means that the field that the user in must be filled out with the correct information.  Validation type; Lookup wizard – is so that the user can choose from a preset range of data, so by choosing one of the given options they are not entering incorrect data. E.g. the ‘title’ of a person; mr, mrs, etc.  Validation type; Validation text – is what is displayed if the user enters the wrong information.  Validation type; Input mask – to prevent the wrong data from being entered. E.g a post code.

Adam Bullock – 8035 19319

Page |3

The table below is showing the symbols and explanations for the input mask that I have used for the post code. Symbol 0 9 # L ? > A A & C <

Description Digit 0-9 (required) Digit 0-9 (optional) Digit or spaces Letter A-Z(required) Letter A-Z (optional) Make following characters uppercase Letter or digit (required) Letter or digit (optional) Any character or space (required) Any character or space (optional) Make following characters lowercase

For the post code I used the input mask of LL09#0LL ERD – Entity Relationship Diagram In order for these two to work together in a relational database a relationship between the tables has to exist. This is done by identifying a primary key field (order number temp from the performance table) and putting this field in the customer table. In the customer table it is called a foreign key. This can be represented graphically. Order

Customer

This is a one too many relationship. Below are the screenshot showing me setting up the relationship.

Adam Bullock – 8035 19319

Page |4

The screenshot on the right shows me setting up the relational database. This is stage one – viewing the different table

The screenshot on the left shows me connecting the two tables together into a relational database.

The screenshot on the right shows me again setting up the relational database; the step shown on the right is the final step.

Adam Bullock – 8035 19319

Page |5

Creating the Database To get all of the information that i was supplied in the dataset into Microsoft access to create my database I had to first split up the ‘tickets’ CSV into two different tables, performance and order.

This screenshot shows the tickets information in Excel, I had to copy and paste the information into two different tables, customer and order. This screen shot shows the customer table, all the information relevant to the customer, name and address etc.

This screenshot shows the order table for my tickets data. All of the information for the performance is listed here, performance name, time etc.

Adam Bullock – 8035 19319

Page |6

After I had decided all of this, I had to import this data into Microsoft Access. The screenshots below show the steps I took to import the data.

This screenshot shows me selecting the excel file that needed importing into access.

This screenshot shows me selecting which sheet of the excel document I want to import into Microsoft access. For this import I chose the customer sheet.

Adam Bullock – 8035 19319

Page |7

This screenshot shows the next step of the import. In most excel documents the first word in the top column are usually the column headers. This is access asking me if I would like to keep the top row cells as the column names.

This next screen shot is of the import wizard asking if I would like to import the excel sheet into a new table or an existing table. As I do not have an existing table I have to put the data into a new one.

Adam Bullock – 8035 19319

Page |8

This screenshot shows me setting the table to have no primary key for the time being.

Adam Bullock – 8035 19319

Page |9

This shot shows me naming the table tblCustomer and clicking to finish the import of this excel sheet.

Adam Bullock – 8035 19319

P a g e | 10