Home ●  Calendar ●  Print

This is an alternate activity to the "Store Your Information" project. Rather than coming up with your own ideas for a database, you will use the data you already have from the Perfume Company Database activity. You will make this database completely web-enabled. My solution and design is included here for you to look at but it is not complete.


You will modify the Perfume Company Database to have a web-based front end (forms and queries) and an administrative back end (database). Permission and security system will be implemented in this database. You will use PHP/MySQL to make the Perfume Company Database web-enabled


Since this will be a team effort, all particpants need to demonstrate understanding. An oral defence will be required to differentiate performance.

Outcome 1:
You (or your team) ) will produce a Documentation (Report or FAQ) that describes the functionality of the database application you have developed and provides an introduction to database models and technology:
1. You will research techniques required to analyse data and develop a web-enabled database.
2. Provide evidence to support an understanding of different data models. Compare and contrast different data model structures and examine their contribution to database development.
3. Examine some case material that focuses on developments such as data mining and data warehousing
Outcome 2:
You (and your team) will design, implement and test a database web application and provide a 16 minute (minimum) presentation to provide evidence of the following:
1. You will transform the existing MS-Access Perfume Company database into a MySQL database.
2. Verify that the design meets user requirements and provide justification of the database design. Your instructor will function as the "user" for this project
3. Use a range of database tools (Acces to MySql Converters, PHPMyAdmin) and techniques to demonstrate a more advanced level of understanding and application
4. Incorporate a query language/languages into the database design (PHP)
5. Use a range of visual tools to enhance the database design
6. Demonstrate queries with PHP/MySQL
7. Provide documentation (functional specification) to support the database implementation
8. Demonstrate ways in which the database has considered the areas of verification and validation (input masking and other techniques)
9. Evaluate a range of testing techniques and apply one to your own database design

You will need to split the roles in the group so that one person does at least one of the following:

1. Expertise in the XAMPP (see below) package; Expertise with designing forms in Dreamweaver
2. Relational databases and how to split the existing perfume database into two or more relational tables. Expertise with MySQL
3. Expertise with PHP and extracting data from and inputting into the database
4. Functional specification of the application; designer of the administration modules; tester of the completed application; Expertise with designing forms in Dreamweaver

Time Frame

This project is complex and will be spread over a period of time which run parallel to other projects. The project starts on Week 8. Oral defenses start on Week 18. Project concludes at the end of Week 18.

The duration of the project is 10 weeks or 2.5 months.



Issues within your teams must be identified and resolved early. Do not try to solve problems within teams at the end of the project!

You will need to start as soon as possible on this project. This is a complex project since you are designing a web-based application for the first time. Each team member will have to spend at least ONE hour per day on this project. There are no shortcuts!!

The teams will be a maximum of 4 students.

You will be assessed on the QUALITY (and quantity) of your work. Your work and efforts will be ranked with the other groups doing this project and grades will be handed to each team depending on how they peform in this ranking.

You will need to figure out the following:


You MUST download a development platform called XAMPP here. This will setup a web server (apache), mysql (database), and php environment on your laptop. You will be able to develop a full web database application without the need to connect to the internet. You will be able to transfer the application to the web when you are ready.

Hint: Before you start building a database you have to have a plan. Make sure you know what you want to do before you make it. Changes afterwards are always a lot more work. The plan is called a "functional specification".

The Library has some excellent books and tutorials on how to built databases using MySQL. There are MANY resources and tutorials on the web that will help you. Some of these are listed below:

URL Purpose
http://mikebernat.com/blog/Setting-Up_a_Relational_Database_in_MySQL Setting up a relational database using PHP/MySQL
http://www.tizag.com/mysqlTutorial/ Excellent tutorial on MySQL
http://www.tizag.com/phpT/ Excellent tutorial on PHP
http://www.freewebmasterhelp.com/tutorials/phpmysql/1 Excellent tutorial on how MySQL and PHP works together
http://ultimatewebsiteshares.blogspot.com/2008/02/php-mysql.html Many links to PHP and MySQL tutorials on the web

BTEC Key Content Areas

Data models and database technology

Models: identify a range of data models and explain their structure and contribution to past, current and future database
developments. Examine why certain models have become obsolete and been superseded by newer models
Types: hierarchical, network (CODASYL), relational
Technology: look at ‘File Based Approach’ systems and DBMS (database management systems) and the contribution they have made to database technology
New developments: look at how databases have evolved to allow for more dynamic storage and search facilities eg data mining and data warehousing
Approaches: top-down — entity analysis techniques, entities, attributes, key identifiers, relationship types and enterprise rules, entity relationship diagrams (ERDs), degrees of relationships. Bottom-up — introduction to problems of redundancy, update anomalies, purpose of normalisation, functional dependency, determinacy diagrams and identification of un-normalised data structures, development of first, second and third normal forms

Design a relational database

Designs: select suitable data types, entity and referential constraints, convert logical database design to physical implementation using appropriate visual tools and structured query languages
Relational requirements: database must be fully relational with evidence of linked tables that are accurate and appropriate to the context of data within the design
Application links: database applications, identification and use of data sources and access via 4GL programs

Manipulation and query tools and techniques

Data manipulation: use of query languages and visual tools for database maintenance, inserts, updates and amendments of
Query and reporting: query languages and query by example (QBE) to extract meaningful data, including formatting and use of
functions/formulae, report writing tools, links to stored queries, design and format of reports, including graphical output

Implement and test

Data analysis: identification of data requirements from different user perspectives, comparison of top-down and bottom-up approaches to data analysis, their strengths and weaknesses
Data definition: documenting results from data analysis, data models/notations, data dictionaries, use of supportive software