Technical Document Template for CA1: Database Design and Development

This technical document template provides an overview of the project scope, entity relationship diagram, assumptions made, data dictionary, technology used, test plan, reflections on learning, references, and SQL code for CA1: Database Design and Development.

CA1: Database Design and Development
Module Title: Databases and Business Applications
Module Code: B8IT101
Module Leader: Jennifer Byrne
Student Name:
Student Code:
1
Jennie Byrne
Technical Document Template

Contents
CA1: Database Design and Development..............................................................................................1
1. Project Overview/Scope............................................................................................................3
2. Entity Relationship Diagram......................................................................................................4
3. Assumptions Made........................................................................................................................5
4 Data Dictionary...............................................................................................................................5
5. Technology Used...........................................................................................................................5
6. Test Plan........................................................................................................................................5
7. Reflections on Learning.................................................................................................................5
8. References.....................................................................................................................................6
9. SQL.................................................................................................................................................6
2
Jennie Byrne
Technical Document Template

1. Project Overview/Scope
The assignment focus on the creation of a database for gym. To successful create the database we
need to first analyse the data provided by the gym owner. To analyse the data we need to list all the
entities the database will have and the columns contained on each entities. Secondly, we will need
to identify the constraint for each column in the entities.
After analysing the data we will build a conceptual design for the database, in this case we will
design an Entity relationship diagram using UML design software. The Entity relations diagram will
be used to show how entities we identified during the analysis process will relate to each other.
Having a clear understanding of how the table will relate with each other, we will start writing the
SQL queries to create our tables. Before creating the table we will create the database where our
tables will be stored. To create the database we will use the SQL data definition language (DDL). The
DDL language is used to create and manipulate database structure.
Next we will start creating our tables inside the database we have created. To create the tables we
will use the DML (data definition language). We can use the INSERT functions to insert data to the
database to ensure our tables are well defined.
The final stage we will test if the database and data tables are working. To test the database we can
install a database server and use it to insert and retrieve the data.
2. Entity Relationship Diagram
Place a copy of your Entity Relationship Diagram (ERD) here.
3. Assumptions Made
List Any Assumptions Made.
4. Data Dictionary
All Data Dictionary based on ERD above.
5. Technology Used
List all technologies used.
3
Jennie Byrne
Technical Document Template

6. Test Plan Table 1 – the test plan
Item Tested Test Run Expected Result Actual Result
7. Reflections on Learning
Proper understanding of database system
Up to this point my knowledge of database system is top. I have understanding of what is a
database, what is contained in database, which data to store into database. I have clear
understanding of which data need to be stored in database management system and the data to
store in file.
Great understanding of relational model
I have proper understanding of what is relational database and how to model data and I can
conceptualize data using data models technique. I have better understanding of algebra relational
operators and I can express SQL queries using relational algebras.
It’s clear from this assignment that I have developed a strong understanding of the database
management system. Am able now to create a database using the procedure methods. I can use the
data manipulation language to manipulate data in the database. I can use sql query method to insert
data, select, update and delete data from the database. Am able now to design the UML diagrams
(ER. User case, class and other UML diagrams) and create relations database from the given entities.
I understand the data integrity constraint and I can specify primary keys, foreign keys, composite
keys and other database constraint while creating tables in database. I can have a broad knowledge
in using SQL languages (DDL) to build database and tables.
Better understanding of data normalization
I have better understanding of data functional dependency and how to normalize data using BCNF
and 3NF using functional dependency.
Even though I have understand how to analyse data from case study and create the entities and the
attributes and define the data constraint. Am still having some difficulties in understanding the
concept of normalizing data using the 3NF form.
With the knowledge of java programming am now able to integrate the database with the java
language,
4
Jennie Byrne
Technical Document Template

8. References
Chris.A .(2000).Advanced SQL Injection in SQL Server Application. An NGS Software Insight
Security Research (NISR) Publication.
Raghu, R & Johaness, G. (n.d). Database management system (2nd ed.). McGraw-Hill
Higher Education
9. SQL
All SQL code can be placed here including Test Data, i.e. insert statements. It may also be submitted
as a separate “.SQL” file in a Zipped up folder.
5
Jennie Byrne
Technical Document Template