Download database design and development and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!
IV Name & Date
Submission Date 15 th^ March 2 020
Issue Date 20 th^ February 2020
Assignment Title
Unit Tutor
Academic Year 2020
Unit Number and
Title
Student Name/
ID Number
I certify that the work submitted for this assignment is my own and
research sources are fully acknowledged.
Student Signature: Date:
Learner Declaration
Higher National Certificate/Diploma in……………………. Assignment Brief (RQF)
The submission is in the form of an individual written report. This should
be written in a concise, formal business style using single spacing and
font size 1 2. You are required to make use of headings, paragraphs and
subsections as appropriate, and all work must be supported with
research and referenced using the Harvard referencing system. Please
also provide a bibliography using the Harvard referencing system. The
recommended word limit is 2, 000 - 2 ,5 00 words, although you will not
be penalised for exceeding the total word limit.
The submission is in the form of a fully functional relational database
system and an individual written report.
The submission is in the form of a technical documentation and a written
Submission Format
Assignment
brief
Case
study
All About Furniture (AAF) is a chain of stores selling an extensive range of household goods from stores located throughout the UK. The company recognises the growing importance of e-commerce in the consumer market and in a bid to remain ahead of its competitors, AAF has decided to expand into this area and offer customers the opportunity to purchase items from a Web site. It should be possible for a customer to log onto the AAF Web site, select and purchase their required items and pay for them. The items would then be delivered to the customer at home. AAF aims to make shopping from its Web site easy for its customer by stocking the goods they require, offering them a wide range of alternatives and keeping then informed about new products coming onto the market. The company requires a database system to meet these aims. The database needs to store information about products in stock and products that will shortly be coming onto the market, to track the stock of products available and to record orders made by the company’s customers. When a customer enters the AAF website, they are presented with a home
Assignment Brief and Guidance
LO 1 Use an appropriate design tool to design a relational database system for a substantial problem. LO 2 Develop a fully functional relational database system, based on an existing system design. LO 3 Test the system against user and system requirements. LO 4 Produce technical and user documentation.
Unit Learning Outcomes
Task 2
2 .1 Once the designs have been accepted by your manager you
have been asked to develop the database system using
evidence of user interface, output and data validations and
querying across multiple tables.
2 .2 You want to include more than just the basics so you will
implement a fully functional database system which will include
system security and database maintenance features.
2 .3 You have decided to implement a query language into the
relational database system. Assessing whether meaningful data
has been extracted through the use of query tools to produce
appropriate management information.
2 .3.1 Display Customer Information
2 .3.2 Finding products from product reference code
2 .3.3 Display Items in the Shopping cart
2 .3.4 Identify the delivery centre of the order
Task 3
3 .1 Evaluating the effectiveness of the database solution
in relation for AAF environment and system
requirements, and suggest improvements
3 .2 Once the system has been developed, you will test the
system against user and system Requirements, including
an explanation of the choice of test data used.
3 .3 Produce technical and user documentation which will be given to
the
AAF. Provide some graphical representations for ease of
reference in
the technical guide and produce a technical and user
documentation for a fully functional system, including diagrams
showing movement of data through the system, and flowcharts
describing how the system works.
D 3 Assess any future be required to ensure the of the database system. M5 Produce technical and user documentation for a fully functional system, including diagrams showing movement of data through the system, and flowcharts describing how the system P 5 Produce technical and user documentation. LO 4 Produce technical and user documentation M4 Assess the effectiveness of the testing, including an explanation of the choice of test data used. P 4 Test the system against user and system requirements. LO 3 Test the systems against user and system requirements M2 Implement a fully which includes system security M3 Assess whether meaningful data has been extracted to produce appropriate P 2 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables. P 3 Implement a query language into the relational database system.
LO 2 & 3
D 2 Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements. LO 2 Develop a fully functional relational database system, based on an existing system design D 1 Assess the design in relation to user requirements. M1 Produce a comprehensive design for a fully functional system which includes interface and output designs, data validations and data normalisation. P 1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements. LO 1 Use an appropriate design tool to design a relational database system for a substantial problem Meri Distinction t Pass Learning Outcomes and Assessment Criteria 5 continued effectiveness improvements that may management information. through the use of query tools and database maintenance. functional database system and system effectiveness of the
who is encourage us to make this. also give us various kind of technologies
advanced knowledge and teach us step by step with her passion to gain us
more and more valuable outcomes such away. I will also get this moment to
thank you once
again.
At the last but not least I will thank my all theMotivated us through there
guidance and advices to given me this opportunity to do
this kind of assignment in standard level to gain our knowledge.
Task 01
1.1 The design of the relational database system using appropriate design
tools and techniques. It should contain at least four interrelated
tables.
Abstract:
Customer can place the order by providing the details of the items.
Customer can check the status.
Admin:
Customer can add products to kart and order them.It will also involve admin panel to
manage the addition of products, editing details related to a product. It will manage
payment gateway also. We will also provide an option to manage offers via offer
management module in the admin panel.
Admin gets login by valid username and password.
Admin can view the request send by customers.
Admin can add new products to the application.
Admin can view all the details of the registered customer details.
Admin can add, edit, update and delete the details of the products and sends
the product to the customer.
Also stock availability and not availability function.
The main purpose of the system is to enable customers to browse and order from any
product that physically inventory has an also through online store to spread among
the all customers with increasing business scope.
The requirement is to develop and implement a fully functional system which will
include interface and output designs, data validations and cover data normalization.
Clear statements of user and system requirements which will allow customers register
and browse through all products online.
Relational model for database system
Is completed by represents put to creates contains completes made makes includes is contained by included Shipment
shipment _id
delivery _date post _code User
user id
username password Payment
payment _id
Payment date Payment method Product order
Product order _desc
Product shop Order
order _id
order _date quantity Total _price Shop
shop _id
address telephone _No email Product
Product Reference code
Product _name Product _description Product _stock Unit _price Costumer
costumer _id
costumer _name telephone _ no address email
Second Normal Form (2NF)
For a table to be in second normal form, the following 2 conditions are to be
met:
The table should be in the first normal form.
The primary key of the table should compose of exactly 1 column.
In my assumption:
First point 1 column primary key. Well, a primary key is a set of columns that
uniquely identifies a row. Basically, no 2 rows have the same primary keys. Let
us take an example.
Login User Product Name Product orders
Usr1 George Black long sofa SKU 0001
Usr2 Salina Wooden 2 drawer table SKU 0002
Usr1 George Revo ARM Chair SKU 0003
Here, in this table, the Product id is unique. So, that becomes our primary key.
Let us take another example of storing User id in various Products. Each User
may purchase in multiple Products. Similarly, each Product may have multiple
Users. A sample table may look like this
(User name and Product id):
User name Product id
George SKU 0001
Malina SKU 0005
George SKU 0002
Salina SKU 0003
Here, the first column is the User name and the second column is the Product
taken by the User. Clearly, the user name column isn’t unique as we can see
that there are 2 entries corresponding to the name ‘George’ in row 1 and row 3.
Similarly, the Product id column is not unique as we can see that there are 2
entries corresponding to Product id SKU 0001 in row 2 and row 4. However, the
tuple (user name, Product id) is unique since a user can Purchase in the same
Product more than once. So, these 2 columns when combined form the primary
key for the database.
As per the second normal form definition, our Purchase table above isn’t in the
second normal form. To achieve the same (1NF to 2NF), we can rather break it
into 2 tables:
Users:
User name User id
George Usr
Malina Usr
Salina Usr
Here the second column is unique and it indicates the User id for the User.
Clearly, the User id is unique. Now, we can attach each of these User id with
Product id.
Products:
Product id User id
SKU 0005 Usr
SKU 0003 Usr
SKU 0001 Usr
SKU 0002 Usr
These 2 tables together provide us with the exact same information as our
original table.
Validation of the compatibility of the data type field length of the
backend database columns with that of those present in the front end of
the application.
Whether the database fields allow the user to provide desired user
inputs as required by the business requirement specification
documents.
Keys and indexes testing
Important checks for keys and indexes -
Check whether the required
Primary Key
Foreign Key
constraints have been created on the required tables.
Check whether the references for foreign keys are valid.
Check whether the data type of the primary key and the corresponding foreign
keys are same in the two tables.
Check whether the required naming conventions have been followed for all the
keys and indexes.
Check the size and length of the required fields and indexes.
Login and user security
The validations of the login and user security credentials need to take into
consideration the following.
Whether the application prevents the user to proceed further in the application
in case of a
invalid username but valid password.
valid username but invalid password.
invalid username and invalid password.
valid username and a valid password.
Whether the user is allowed to perform only those specific operations which are
specified by the business requirements.
Whether the data secured from unauthorized access
Whether there are different user roles created with different permissions.
Whether all the users have required levels of access on the specified Database
as required by the business specifications.
Check that sensitive data like passwords, credit card numbers are encrypted
and not stored as plain text in database. It is a good practice to ensure all
accounts should have passwords that are complex and not easily guessed.
Risk quantification -
Quantification of risk actually helps the stakeholders to ascertain the various
system response time requirements under required levels of load. This is the
original intent of any quality assurance task. We need to note that load testing
does not mitigate risk directly, but through the processes of risk identification
and of risk quantification, presents corrective opportunities and an impetus for
remediation that will mitigate risk.
Minimum system equipment requirement-
The understanding which we observe through formal testing, the minimum
system configuration that will allow the system to meet the formal stated
performance expectations of stakeholders. So that extraneous hardware,
software and the associated cost of ownership can be minimized. This particular
requirement can be categorized as the overall business optimization
requirement.
System output screen shots
Location and contact details
