Download Design a database using SQL and more Summaries Database Programming in PDF only on Docsity!
Plagiarism
Plagiarism is a particular form of cheating. Plagiarism must be avoided at all costs
and students who break the rules, however innocently, may be penalized. It is your
responsibility to ensure that you understand correct referencing practices. As a
university level student, you are expected to use appropriate references throughout
and keep carefully detailed notes of all your sources of materials for material you
have used in your work, including any material downloaded from the Internet. Please
consult the relevant unit lecturer or your course tutor if you need any further advice.
Student Declaration
I certify that the assignment submission is entirely my own work and I fully
understand the consequences of plagiarism. I understand that making a false
declaration is a form of malpractice.
Student signature: Date:24/12/
Cotent
Plagiarism............................................................................................................................................ 1
Student Declaration............................................................................................................................. 1
Assignment Submission and Declaration
Student ID (^) P766568 Student Name YangZhen
Higher National in
Certificate/Diploma
□ Business ■ Computing
Unit No. & Title Unit 4 Database Design & Development
Academic Year 2021-2022 Semester 1
Assessor Zeng Xianhui Internal Verifier Jiahui Lu
Assignment No. 1/
Assignment
Format
Database design
documentation, application
developing documentation
and an implementation of
DVD rental tracking and
query system
Assignment Title
Database design and Development of DVD rental tracking and
query system
Issue Date
September 29,
2021
Submitting Deadline January 5, 2022
Actual submission date 19/12/
- Student signature: Date:24/12/2021....................................................................................................
- Database design of DVD rental tracking and query system...........................................
- Application design document of DVD rental tracking and query system....................
- Application test document of DVD rental tracking and query system.........................
- User manual document of DVD rental tracking and query system.............................
HTV needs a medium-complexity database system that needs to be met
- Search function: Provide adequate support to customers when searching for
movies/actors, manage customer leasing transactions, and support back-end
processes.
- Data import: The manager requires that all data, including information about
movies, customers, employees, etc., be imported from an Excel file.
Checking the rental status: Managers can easily check the rental status.
Log in function: In order to protect the security of data, this application can only be
used by authorized users, who need to log in to the application.
- The Database System can be used by the Windows 10 operating system.
3.Concept model design using ERD
Convert the information collected above into an ER diagram Figure 4:
n
n 1
m
n
n
city
address first name
state
member id
Customer
zip code
last name
Produce
payment type
rental email
Rental
information (^) Manage
Employee
phone
rental data
employee id
name rental id due date
Contain
collected late
data returned
leading actor
accrued late
DVD
DVD number
year
category code
Movie
category
classify
movie title
rating
rcode
movie category
Customers
Title Type Size Restraint
customer id varchar 15 Main code
first name varchar 30 Not null
last name varchar 30 Not null
address varchar 50 Not null
city varchar 30 Not null
state varchar 15 Not null
zip code int 10 Not null
Table 1 Customer table
Rentals Information
Title Type Size Restraint
rental id int 10 Main code
DVD number int 10 Not null
due date datetime 30 Not null
data returned datetime 30
rental fee decimal 10 Not null
Late fee decimal 10
accrued late int 10
Table 2 Rental Information table
DVD
Title Type Size Restraint
DVD number int 10 Main code
movie title varchar 50 Not null
year int 10 Not null
category code varchar 10 Main code
leading actor varchar 50 Not null
Table 3 DVD table
Employee
Title Type Size Restraint
employee id int 10 Main code
name varchar 50 Not null
email varchar 50 Not null
phone varchar 30 Not null
Table 4 Employee table
Movie category
Title Type Size Restraint
category code varchar 10 Main code
category name varchar 30 Not null
rating varchar 10 Not null
Table 5 Movie category table
Rental
Table 6 Rental table
Loginuser
Table 7 Loginuser table
4.2 Design the relational data model using normal theory and model
decomposition technique
After analysis, the following relationships can be obtained:
HTV (rental id, DVD number, customer id, first name, last name, address, city,
state, zip code, due date, data returned, rental fee, late fee, accrued late, movie
title, movie year, leading actor, category code, rating, employee id, name, email,
phone, category name, rental date, customer id, employee id, pay type)
Paradigm Analysis:
- 1NF: It cannot be divided into other columns, and all the fields in the database
table are single attributes and cannot be separated.
Title Type Size Restraint
Rental id int 10 Main code
rental data datetime 50 Not Null
customer id varchar 30 Not Null
employee id int 30 Not Null
pay type varchar 50 Not Null
Title Type Size Restraint
username int 10 Main code
password int 10 Not Null
DVD (DVD number, movie title, movie year, leading actor, category code)
Employee (employee id, employee name, employee email, employee phone)
Movie Categories (category code, category name, rating)
Rental (rental id, rental date, customer id, employee id, pay type)
4. System functions and interface Design
5.1 Analyzing the data flow of the system
Data flow diagrams reflect the process of work in objective real-world problems
It uses simple graphics to represent data flows, processes, data sources, and
external entities, etc., without involving any specific physical elements, but only
describes the flow and processing of data in the system.
User
Information data
filename
customer name
failed username keywords
import rental
failed information
rental rental
information
Rental Information
Excel Files Database
Figure 5 The data flow diagram
According to the Figure 5 data flow chart, Customer enters a username and password
to log in, the user information is exported from the user information database, and the
authentication failure returns to the login interface and prompts the user with an error
password. After successful verification, you can query the rental information. The
Login
Query
analysis
Import
Customer
information contains rental time, fees, DVD, and movie information, which is
displayed to the consumer after the query is successful. The rental information is
imported from the Excel form into the rental information database and uploaded to the
rental information query system. DVD information database storage, DVD
information and imported into the rental information query system, entered by
employee. Employees can use the administrator account to manage and query the
system background data.
5.2 System functions and interface design
(1) Login interface
See figure 6 below the user logs in to the interface system, enters the user name
and password, and can enter the system after authentication.
Figure 6 Login interface
(2) Feature selection interface
See figure 7 below the system menu page has three functions: "Rental
information import", "Rental information analysis", "Exit".
See figure 9 below Rental information analysis interface, after entering the keyword,
click "search", you can query the corresponding order.
Figure 9 Data query interface
5. Validating the effectiveness of the database design
When the user uses the completed HTV rental database system, he first needs to
enter the correct username and password in the login interface, and the system will
enter the database system after successful system authentication. On the main
interface, you can select the input import or query function. When the user selects the
data import function will enter the import interface, select the correct Excel file, the
data will be displayed in the text box on the right side, click Save, the data on the
Excel file will be saved in the Access database. When the user selects the query
function, you can select keywords according to your needs, such as: time, customer
number, movie name, employees and other information, the system will read the data
stored in the database and find relevant information according to the keywords. For
example: select the time 1/1/2021-1/12/2021, the system will extract the database
data and display all the information during this period, displayed in the text box.
The system includes: the user logs in to the system, extracts the database Login
user data, and verifies the username and password. Data import function, which
imports Excel file data into access database. The data query function extracts relevant
data information from the database according to the keyword query. The above
functions meet the requirements of HTV.
6. Conclusion
Customer demand analysis is very important in the database system,
communication with customers can get their needs, clear user information also need
to collect data, and then make ER diagrams based on the collected data. The ER graph
is then transformed into a data-relational model, and the various properties of the
relationship are normalized. Make a data flow diagram, and finally make a system
through VB.net, which needs to meet the functions required by customers.
Figure 11 Feature selection interface
(3) Data import interface
See figure 12 below the Rental information import interface selects "select files"
to select the file to import, and "save to Database" saves the imported file.
Figure 12 Data import interface
(4) Data query interface
See figure 13 below rental information analysis interface, after entering the keyword,
click "search", you can query the corresponding order.
Figure 13 Data query interface
3.System function design
3.1 System general design
The frame diagram simply depicts the HTV DVD rental system:
According to the data flow chart, the HTV rental database system has the following
framework diagram Figure14 functions:
Figure 14 System Function Framework Diagram
DVD rental
information
Login Main Analysis
Yes
Figure 15 Login module flowchart
As shown in Figure16 below, the data Import module of the HTVRental system:
No
Yes
Execute SQL
statements
Verify right
and wrong
Enter the main
interface
End
Start
Select the
Excel file
Verify
the file
Generate SQL
statements
No
Yes
Figure 16 Flowchart of the data import module
As shown in Figure17 below, the query module of the HTVRental system:
No
Execute SQL
statements
Data display
Save the data
Complete
the import
End
Start
Enter
keyword
Generate SQL
language
Execute SQL
statements
