The following activities must be performed:
- Design the tables and identify Primary and Foreign Keys
- Define the relationships between the tables
- Create suitable data entry forms
- Enter Test Data
- Create at least five relevant queries to interrogate the database
- Create and print reports that present a consistent and professional image which are accurate and complete
INTRODUCTION
I have been given the task to design a database for a company call Moving images.
Moving images is a company that rents out DVDs for their register member, each member can take a DVD and that information will be store in to the system until the DVD has been return.
REQUIREMENTS OF THE NEW SYSTEM
Moving images is a DVD shop (to rent DVDs), as we been informed the company is growing and so are the numbers of DVDs and the number of customers which means the staff need access to this information faster than before.
The management of Moving Images is requesting prototypes from different designers.
Database will allow the user to enter new and view existing information in to the system using Microsoft Access.
USER REQUIREMENTS
These are the specification for the system, what the user needs to get from the system
- Rent DVDs (only to register members)
-
Each DVD must have at least one copy. (Must have DVD titles, actor director, producer, rating and category)
-
Different categories. (Must have sci-fi, comedy, Action, education, foreign films, family and thriller)
-
DVDs can only be rented to register member. (must have member name, address, and telephone number)
-
Surcharge must be applicable for DVDs that are return late. (50% surcharge of the original price, it could either be daily or as the total without counting how many days the DVD was late)
BOLD = POSSIBLE ENTITIES
ADVANTAGES OF COMPUTER BASED SYSTEM
There are many advantages of using a computerized system for Moving Images they are:
The files can be backed up, if the computer is affect by a virus or the system crashes or if there is a fire, then no all files will be lost .
It could be update or change more easily.
The files could be found more easily.
MOVING IMAGES DATABASE DESIGN
ENTITY RELATIONSHIP DIAGRAMS – CONCEPTUAL MODEL
As we can see on the conceptual model below, there is a M:N relationship, a database cannot be create when there is a M:N relationship.
ENTITY RELATIONSHIP DIAGRAM – LOGICAL MODEL
On the logical model below, we can see that the M:N relationship is no longer on our diagram.
The way I solved this was by creating a intersection entity called loan, this table will have a primary key of its own and two foreign keys, one from the members table and the other one from the DVDs table that way the relationship between the three tables will be create.
DATABASE SCHEME
UNF
MEMBERS: (MEMBER_ID , TITLE, F_NAME, M_NAME, L_NAME,
ADDRESS, COUNTY, POST_CODE CONTACT_#,
DATE_JOINING, EXPIRED_DATE)
DVDs: (DVD_ID, DVD_TITLE, DIRECTOR, ACTOR,
, PRODUCER)
1NF
MEMBERS: (MEMBER_ID , TITLE, F_NAME, M_NAME, L_NAME,
ADDRESS, COUNTY, POST_CODE CONTACT_#,
DATE_JOINING, EXPIRED_DATE)
DVDs: (DVD_ID, DVD_TITLE, DIRECTOR,
CATEGORY_DESCRIPTION, RATING, PRODUCER)
LOANS: (LOAN_ID, MEMBER_ID, DVD_ID, QUANTITY, PRICE,
DATE_OUT, DATE_IN, DATE, SURCHARGE, DAYS_LATE,
TOTAL)
CATEGORY: (CATEGORY_DESCRIPTION)
2NF/3NF
MEMBERS: (MEMBER_ID , TITLE, F_NAME, M_NAME, L_NAME,
ADDRESS, COUNTY, POST_CODE CONTACT_#,
DATE_JOINING, EXPIRED_DATE)
DVDs: (DVD_ID, DVD_TITLE, DIRECTOR
,
CATEGORY_DESCRIPTION, RATING, PRODUCER)
LOANS: (LOAN_ID, MEMBER_ID, DVD_ID, QUANTITY, PRICE,
DATE_OUT, DATE_IN, SURCHARGE, DAYS_LATE,
TOTAL)
TITLE: (TITLE)
CATEGORY: (CATEGORY_DESCRITION)
RATING: (RATING)
MOVING IMAGES DATA DICTIONARY
MEMBERS TABLE
DVDs TABLE
LOANS TABLE
TITLE TABLE
CATEGORY TABLE
RATING TABLE
IMPLEMENTATION
TABLES
This design will have six tables.
Members this is where all the customers personal information is going to be store.
DVDs this is where all the information about the DVDs will be store.
Loans this is where all the information about the loans will be store.
Title this is where the customers title will be store (Mr, mrs, miss).
Category this where all the DVDs categories will be store (comedy, action).
Rating this is where the rating for each DVD will be store (12, 15, and 18)
MEMBERS TABLE
This table includes the following fields:
To the right we can see a copy of the table on design view, most of field use the same data type, which is text, including the telephone number, the reason for this is because the numeric data type will not allow a leading zero as text data type will.
On members id the data type is autonumber, which is a numeric number automatically, generate by the system and the user has not control over it, and this is also the primary key as not other customer will have the same number.
Joining date and expire date are the same data type, but as we can see below the setting are very different.
On the joining date I used as default value Date() which means the actual day that is set in the computer, it will change as soon as the date on the computer changes.
on the expired date the customer is given 1460 days or three years and the default value is Date()+1460 which means that it is the actual date plus 1460 days.
I also got a foreign key (title) this will crate the relationship between the title table and the customers table.
One title can be use by many customers
DVDs TABLE
On the DVDs table I also used autonumber for DVD_ID because it becomes the unique identifier for that DVD and also becomes the primary key for this table.
I also have two foreign keys from different tables one foreign key is: category_description from the category table and the other one is rating from the rating table.
This two foreign keys will create the relationship between the three tables.
One rating can be use by many DVDs and many DVDs can use one category.
LOANS TABLE
In the loan table I used the autonumber for the loan id which will be unique and because is unique it will become the primary key for this table.
The two foreign keys are member’s id from members table and DVD id from the DVD table.
With this two foreign keys the relationship between the three tables will be possible
One member can take many loans and many loads can have one DVD.
TITLE TABLE
This table is only one field (title) and that title being the only one becomes the primary key of that table and the foreign key in the members table to create the relationship.
CATEGORY TABLE
This table also has one field (category_description) and being the only one it becomes the primary key on this table and then becomes the foreign key on the DVD table to create the relationship between the two tables.
As we can see below I have use a validation rule so not other data can be enter onto the system .
RATING TABLE
On this table I also have only one field, which is rating , being the only one and like in all the other tables where I only got one field this will become the primary key on this table and the it will become the foreign key on the DVDs table to create the relationship between the two tables.
RELATIONSHIP BETWEEN THE TABLES
PROTOTYPES OF USER INTERFACE
My design has several forms and they are divide in two. the ones where all the information can be edit and the others ones is where the user can enter new information without altering the information that is already store in the system, as we can see in picture 1.1 this form is the main form to enter new information and one of the commands is “edit existing information”.
PICTURE 1.1
The command “edit existing information" is where the information for the managers is stored.
The reason for doing this is that only the manager will be able to edit any information that has already been stored in the system, so I achieve this by creating a username and password form and we can see in picture 1.2.
PICTURE 1.2
After the username and password has been enter the manager or the person in charge will be able to enter the second form where all the information can be edit or erase, as we can see in picture 1.3
.PICTURE 1.3
On my design, each DVD has multiples copies and each DVD belongs to a category and rating.
On the loans form in the managers menu we are able to see which DVDs has not been return and how much the surcharge is.
On the member form (manager’s menu), we can see all the details of the customer and their personal information.
On the DVD table on the manager’s menu, we can see all the detail of the DVDs that we currently have in Moving Images.
VERIFYING AND EVALUATING MY DESIGN
MOVING IMAGES
Moving Images operates a DVD library. The library has a large number of titles, each title having at least one copy. Each title falls into a specific category some of these are adventure, thriller, fantasy, action or education. (There are others)
All titles are only loaned to registered members of Moving Images. Information is store on members personal details including name, address and contact number.
Any titles overdue incur a surcharge of 50% of the loan fee.
As an employee of a software company I am required to design a Relational Database for Moving Images. The client requires that information is stored so that they can maintain details of all of their DVD’s, they wish to track loaned titles and check on overdue titles. They also require the facility to produce ad-hoc reports.
You need to use appropriate Data Analysis and Database Design Techniques to structure your data and build the database system. Notes must be made in all stages of the process from Analysis through to Implementation and Testing.
POINTS TO REMEMBER
- Design for DVD library.
- Each title must have a least one copy.
- Each title must have a category
- Only registry members can loan DVDs
- Must keep records of all members.
If a DVD is return late, a late surcharge must be applicable.
TESTING BUTTONS
Here I am testing all the buttons, this command buttons are the one the user is going to use to move from form to form o to make any kind of queries or report
TESTING THE QUERIES
I am going to test all the queries to make sure that all of them are working properly.
I am going to start with the loans query, o this query we will retrieve from the system all the information on the DVDs that are on loan a this moment.
I have set up two parameter for the search of this query, one of them is for the member id and the other one if for the DVD id.
The set up the parameter all I did was write this following criteria on the criteria option on the query:
Like”*”&[enter member id]&”*”
like”*”&[enter DVD id]&”*”
like”*”&[enter actor name]&”*”
Like”*”&[enter DVD title]&”*”
This is the interface that the user will be able to see after the parameter for the query has been entered.
On the query for the DVDs, I used four different parameter of criteria to search if none of the criteria is enter then it will displays all the DVDs on the system.
Here is asking for the DVD id and I set up this parameter by written this on the criteria:
Like”*”&[enter DVD id]&”*”
like”*”&[enter DVD title]&”*”
like”*”&[enter director name]&”*”
like”*”&[enter actor name]&”*”
This in the interface that the user will see after the search for the query has been finish.
On the members query I have set up three different parameter to allow the use easier access to the information the may need.
Like”*”&[enter member id]&”*”
like”*”&[enter customer id]&”*”
like”*”&[enter customer post code&”*”
This is the user interface:
TESTING REPORTS
This is preview of the report for all the DVDs on the store.
The user must use the navigations buttons to move from record to record.
This is preview of the report for all the DVDs on the store.
The user must use the navigations buttons to move from record to record.
This is preview of the report for all the DVDs on the store.
The user must use the navigations buttons to move from record to record.
REPORT BASED ON QUERIES
- LOANS REPORT BASED ON QUERY
This is very similar to using the queries form, all I am doing is asking the program to get me the information I need and the parameter will help me do that.
The only different is that I am going to get this information as a preview of a report.
The same set up of parameter used on the query are going to be use on the report, and the program will search for the following criteria
Like”*”&[enter member id]&”*”
like”*”&[enter DVD id]&”*”
like”*”&[enter actor name]&”*”
Like”*”&[enter DVD title]&”*”
On the report, the user must use the navigation buttons to move from record to record.
The report will look like this:
- DVDs REPORT BASED ON THE QUERY
On this one, the same criteria that we use on the query like in the explanation before the different is that the user will see a report instead of the DVDs query form.
Like”*”&[enter DVD id]&”*”
like”*”&[enter DVD title]&”*”
like”*”&[enter director name]&”*”
like”*”&[enter actor name]&”*”
The user must use the navigations button to move to another record.
The report will look like this:
- MEMBERS REPORT BASED ON THE QUERY
On the members report like in the query I have set up three different parameter to allow the use easier access to the information the user may need.
Like”*”&[enter member id]&”*”
like”*”&[enter customer id]&”*”
like”*”&[enter customer post code&”*”
Like in the other reports, the user must use the navigations buttons to move from record to record.
The report will look like this:
EVALUATION
USER REQUIREMENTS
These are the specification for the system, what the user needs to get from the system
This user requirement has been met by creating a form on the main menu called new customer, this form will allow the user to enter the information of new customers and the information is then store into the system on the members table.
This user requirement has been met by creating command button on the main menu call existing member, this command button will then open the manager’s login and then the user will be allow to enter to the area where all the information for the customers has been stored and edit any information for an existing customer.
This user requirement has been met by creating in a very similar way a form on the main menu called new DVDs and it will work in the same way the new member form does.
Any new DVD that is enter in to the system will then be save in the DVDs table.
This requirement has been met by creation a command button on the main menu, very similar to the way the user changes members details, a login form will then open and after the user has login the second menu comes up and in there will be a command button called existing DVDs, this will open a form for the user to find any DVD store in the system and changes any information about that DVD.
- Rent DVDs (only to register members)
When a customer will rent a DVD the customer id must be provide if the customer is not a register customer but a potential customer he would be ask to register with moving images otherwise not DVD would be rented to that person.
This could be done through a query (loans query), this will show the user all the information about any DVD that has not been return yet and also will be able to find if any DVD is late for return and how much the surcharge is.
The daily surcharge for any DVD that is return late is £1.50.
