Saturday 11 February 2017

Database Design Process

Welcome to my blog. In this first Post i will be talking about the different stages that are needed in order to design and implement a functional Database that meets the users requirements.

Requirements Analysis
Requirements are needed in order to make sure you know what the users needs or wants. Requirements also help the design people to know what they are trying to achieve and what they need to include in the design process. A requirements document should be made with the owner of the company or the manager who know what they want, the company who will be developing the database will need to know everything about the company and what they need so the chief from he development company will have a long chat with the company that needs the database. Also knowing what the user needs is very vital in the next processes.


Development Life Cycle
Development life cycle shows clearly how each stage works and how each database project will follow before reaching its final stage into the world, here is the development life cycle:

There are in total 5 stages but in some cases it can be even more. So the Requirements Analysis is the first on the list this is where the database project manager talks to the company owner about the needs for their database. It will all get recorded and put into a document for the next stage which is the Design, the design is a very important stage and a few people will be working on it by producing the Physical and Logical diagrams, Entity Relationship Diagrams, Data Dictionaries everything to make sure the database is ready for the next stage. The next stage is Implementation or in other words making/creating the database into a functional one so in this stage this is where the database comes to life and is ready for the next stage.

The next stage and the most important in any database projects is testing, the testing will make sure the database if functioning properly and that its meeting its user requirements which is very important if the test fails in the Evaluation stage which evaluates on how the database functions and looks, it will then got though the process again till eventually the database is perfect and meets all the users specifications.

Database Designer working with a expert in domain Development
Databases are very easy to design but you need people that know what they are doing and when designing a database it is very important to stay  focused. As i said before the Requirements Analysis is very important as it will help other stages and this is where it comes in, the requirements will be used in order to design the database based on the users requirements, this can be from making the background Pink to adding Passwords on every page either way the designers will do it.

Professional database designers will draw out the perfect database with notes to help the developers in the later stage, also if there were any changes made to the design it will be clearly written on the design brief to make sure the developers know whats happening. The design document will go to the user who need the database designed and look for any changes or improvements if its all good then its on to the next stage.

Designers will also work with professionals in the domain development stage, why? This is because some designers are only good for the main design of the database and following the user requirements but experts are needed for domains because its where the database will go live and where it will collect its data. The experts will be able to programme the database and make sure the domain is correctly set up for its functionality. Having an expert is one thing but if people are needed for different departments it would be beneficial for any company to hire people who are skilled in those departments rather than letting a designer create and develop a domain.


Tables - Tables are very important in a database because its where the data is stored and how it will effect the rest of the designing stages. When creating tables the designer needs to make sure that the top fields which might include Name, Address and other information needs to be on top to make sure when the data is filled in and saved it saves int he correct tables. The other thing is to add a Data Validation to make sure data is valid from any errors and mistakes. Another thing is that the designer might add input masks such as number ones to make sure the user only fills in 11 numbers not 15.


Data Types - There are many data types that can be chosen from some of them are Numbers which means only numbers can be filled into the field, the other can be a Yes/No which basically has a drop down with only Yes or No and the 3rd one which is commonly used by many designers is Lookup Wizard, this allows the designer to use data from other tables which will be shown in a drop down box when selecting, with that said it brings me to the next point.


Relations - Relations are very similar to Entity Relationship Model as the model is drawn out to show how each data will be connected but in a Relations Model it organises the data in to one or more tables within the database of columns and rows, each of those will have a unique key identifying each row. Rows can also be called records as its where that data is stored and also each table or relation will represent one entity type such as a customer or a product depending on what your database is.


Indexes - Indexes is a type of search that is made on the database to search for a specific data but instead of searching through the whole database, you would have a table that has all the data and the way it searches is for the specifics so if you had a flight number the database will search for the flight number in another table then the data will appear specified to that Flight Number.

Primary/ Foreign/ Compound Keys - Primary keys are used to make a ID that is only specified to that table meaning that its not linked to any other table. A foreign key is when you're trying to use another tables ID this means its using another tables information and showing it on a another table. Compound Keys are

Entity Relationship Modelling - The way that the design people know how a database will work is by drawing up a data relationship diagram from he specifications this will be drawn by some boxes with titles and with the fields that would be included then lines that connect to other tables which shows how the data is linked together, this also helps the designers to easily create a database.

Normalisation - This is used in ALL databases and what it does is it make sure that no same data in the tables field is the same in other ones, the reason for this would be so that data won't be stored in other tables as it will mess up the Reports. It also makes sure the database is fool proof to any mistakes.

First Form - A first form normalisation ensures that the database is kept safe from any repeating groups within the database and contain values that cannot be divided. So if you have table with colour green,red in the same column this will be wrong because you are having 2 products in the same column, so in order to make sure there isn't you will split them into another table and make them separate.

A repeating group means when the table contains two or more columns that a very similar to each other so if you have Book information and it starts with BookID, Author 1, Author 2... and so one these all are the same or similar to each-other.

Second Form - A second form normalisation must be following the first from normalisation in order to be successful. So firstly in the second form normalisation any of the tables must not have any partial dependency in any of the columns on primary key.

Third Form - A third form of normalisation follows the second form normalisation  and also  for it to be successful it must no have transitive functional dependency which means if A is functionally dependant on B then the B will be functionally dependant on C but then the c is transitively dependant on A though B.


Domain Development Area
This is where the Domain is created for the company that will be using the data base, the domain needs to be able to withstand a lot of data which might be filled din by the users of the database. The other thing that is important is making sure that its very secure, some data might be very confidential and needs protecting from Hackers or users who simply don't have the access to it! Another thing that needs to be taken into consideration is that the domain needs to make sure it works with all the functions inside the database, if they don't then the database won't work correctly.

Requirements Specification  
Every stage especially after the design stage the person in charge of the project needs to make sure all the requirements are specified correctly this means the person in charge and the designers work together to note all the functions of the database from what an Exit Key does to where to insert the password fields. This helps the people at the implements side which I'm going to talk about next.

Logical Design
A logical design would be made on a program or drawn up to show how the data links together and what each field contains. It will have boxes and text inside which will identify each table with a Title and what it will do then lines that will connected to other tables which also are identified with a Title and a short description of what it does.

Physical Design 
A physical design is a complete design of a Database which shows the functionality it is created by using SQL which is used by many people in creating databases. For relational databases it will be much easier to translate the logical data model into a physical database. One of the rules that anyone that tries to translate the logical data into physical is that the entities become tables i the physical database.