The database that I have created with this case study I manage to reduce the duplication of
some of the data by normalising the data that was given. First thing that I did with the data was
make sure that the data was atomic and that every field had its own unique name. Also I made
sure that every table had its own primary key e.g for my parties table the primary key for it was
Party ID. customers have their own unique ID so this also reduces data duplication for the data
of the customers. My customer table and party sales have customer ID and party sales ID as
their primary keys. In the party sales ID I have customer ID and party ID which are foreign keys
which helped me link the data.
Within the table structures I have also added validations which will reduce errors when inputting
data into the database. For example a surname has a presence check where a surname cannot
be left blank. The validation rule I used for this is: Is Not Null. The postcode in my customer
table also has a validation which is a format check. The validation rule I used for the postcode is
LL0 0LL which will not let the user type in a number when there should be a letter inputted. In
the party sales field I have set up a lookup wizard e.g party ID, party description, party date. This
will prevent errors when the user is typing or selecting a party description. This will also speed
up data entry as the user are selecting the data and not manually typing it. There are further
validations for example the number of tickets. This field has a range check set e.g a customer
can only buy a ticket between 1-8. This also reduces errors.
I have also included validation texts for the fields that will help the user understand what to do
when they make an error. I also sorted the data in an ascending order to make mydata more
organised and efficient to read. This has increased productivity in my database. An example of
this is that i've created a query that searches for parties between 26/04/21 and 27/04/21. This
increases productivity as the users will not need to search for the data. I've also increased
productivity in terms of calculating income generated by using calculated fields in another field.
For example number of tickets*party ticket price. I have also used various functions to calculate
values. For example the total number ticket sold I used a sum function equal to sum.
=sum(),=max(),=min(),=count(). I have also used criteria where I can search for vip and
standard. I have created a report on the total income generated for these parties. Reports are
suitably laid out for users to read easily. The reports also have calculated fields.
In conclusion I think my database is user friendly because of the lookup values and validation
rules that have been applied in this database. Lastly my database has been thoroughly tested to
make sure it is efficient and reliable. I believe ive met all the requirements by creating the 3
tables: customer, parties, and party sales. I've managed to do the 2 queries and furthermore, I've
managed to do the report. Overall I believe I have managed to achieve the requirement for this
case study.
some of the data by normalising the data that was given. First thing that I did with the data was
make sure that the data was atomic and that every field had its own unique name. Also I made
sure that every table had its own primary key e.g for my parties table the primary key for it was
Party ID. customers have their own unique ID so this also reduces data duplication for the data
of the customers. My customer table and party sales have customer ID and party sales ID as
their primary keys. In the party sales ID I have customer ID and party ID which are foreign keys
which helped me link the data.
Within the table structures I have also added validations which will reduce errors when inputting
data into the database. For example a surname has a presence check where a surname cannot
be left blank. The validation rule I used for this is: Is Not Null. The postcode in my customer
table also has a validation which is a format check. The validation rule I used for the postcode is
LL0 0LL which will not let the user type in a number when there should be a letter inputted. In
the party sales field I have set up a lookup wizard e.g party ID, party description, party date. This
will prevent errors when the user is typing or selecting a party description. This will also speed
up data entry as the user are selecting the data and not manually typing it. There are further
validations for example the number of tickets. This field has a range check set e.g a customer
can only buy a ticket between 1-8. This also reduces errors.
I have also included validation texts for the fields that will help the user understand what to do
when they make an error. I also sorted the data in an ascending order to make mydata more
organised and efficient to read. This has increased productivity in my database. An example of
this is that i've created a query that searches for parties between 26/04/21 and 27/04/21. This
increases productivity as the users will not need to search for the data. I've also increased
productivity in terms of calculating income generated by using calculated fields in another field.
For example number of tickets*party ticket price. I have also used various functions to calculate
values. For example the total number ticket sold I used a sum function equal to sum.
=sum(),=max(),=min(),=count(). I have also used criteria where I can search for vip and
standard. I have created a report on the total income generated for these parties. Reports are
suitably laid out for users to read easily. The reports also have calculated fields.
In conclusion I think my database is user friendly because of the lookup values and validation
rules that have been applied in this database. Lastly my database has been thoroughly tested to
make sure it is efficient and reliable. I believe ive met all the requirements by creating the 3
tables: customer, parties, and party sales. I've managed to do the 2 queries and furthermore, I've
managed to do the report. Overall I believe I have managed to achieve the requirement for this
case study.
