Currently Carlos is in need of two basic tables, supplier and product. He currently has three suppliers but as his business continues to grow he expects to have more. The database needs to easily be able to add new suppliers and products without interfering with the integrity of the data or the database structure.

Data Modeling

Using the following diagram we can see that we will be creating two tables; suppliers and products.

We are creating a one-to-many relationship because each supplier may have several products, but each specific product can only have one supplier. We also are going to guarantee that products have to be associated with suppliers, and suppliers have to be associated with products. A supplier is of no use without a product and a product can't come into the door without a supplier.

Normalization

We have reviewed the design to 3NF (Third Normal Form) and find that we are good. The Third Normal Form states that we must meet the requirements for a First Normal Form (no duplicate columns from the same table and we have separated are data into two tables), Second Normal Form (our database is too small at this time to really worry about this), and the rules for the Third Normal Form (all of our columns are fully dependent upon the primary key.

Conclusion

Upon meeting the three requirements we can now safely create the database. With the design finished and compared to the rules of the Third Normal Form we can assuredly build a secure database that will grow as needed as Carlos' business grows.

References
Hernandez, M. (2003). Database Design for Mere Mortals Second Edition. Boston, MA: Addison-Wesley.

RDBMS Terminology

Relational data consists of relations. A relation (or relational table) is a two dimensional table with special properties. A relational table consists of a set of named columns and an arbitrary number of rows. The columns are called as attributes and rows are called as tuples. Each attribute is associated with a domain. A domain is a set of values that may appear in one or more columns.

Properties of Relational Data Structures

Relational tables have six properties, which must be satisfied for any table to be classified as relational. These are:

1. Entries of attributes are single valued: Entry in every row and column position in a table must be single valued. This means columns do not contain repeating groups.
2. Entries of attribute are of the same kind: Entries in a column must be of same kind. A column supposed to store salary of an employee should not store commission.
3. No two rows are identical: Each row should be unique. This uniqueness is ensured by the values in a specific set of columns called the primary key.
4. The order of attributes is unimportant: There is no significance attached to order in which columns are stored in the table. A user can retrieve columns in any order.
5. The order of rows is unimportant: There is no significance attached to the order in which rows are stored in the table. A user can retrieve rows in any order.
6. Every column can be uniquely identified: Each column is identified by its name and not its position. A column name should be unique in the table.

Characteristics of DBMS

Control of data redundancy: When the same data is stored in a number of files it brings in data redundancy. In such cases, if the data is changed at one place, the change has to be duplicated in each of the files.
The main disadvantages of data redundancy are:
1)Storage space gets wasted.
2)Processing time may be wasted as more data is to be handled.
3)Inconsistencies may creep in.
4)DBMS help in removing redundancies by providing means of integration.

Sharing of data: DBMS allow many applications to share the data.

Maintenance of Integrity: Integrity to data refers to correctness, consistency and the interrelationship of data with respect to the application that uses the data. Some of the aspects of data integrity are:
1)Many data items can only take a restricted set of values.
2)Certain field values are not to be duplicated across records. Such restrictions, called primary key constraints can be defined to the DBMS.
3)Data integrity which defines the relationships between files is called referential integrity rules, which can also be specified to the DBMS.

Support for Transaction Control and Recovery: Multiple changes to the database can be clubbed together as a single ‘logical transaction’. The DBMS will ensure that the updates take place physically only when the logical transaction is complete.

Data Independence: In conventional file based applications, programs need to know the data organization and access technique to be able to access the data. This means that if you make any change in the way the data is organized you will also have to take care to make changes to the application programs that apply to the data. In DBMS, the application programs are transparent to the physical organization and access techniques.

Availability of Productivity tools: Tools like query language, screen and report painter and other 4GL tools are available. These tools can be utilized by the end-users to query, print reports etc. SQL is one such language, which is emerging as standard.

Control over Security: DBMS provide tools by which the DBA can ensure security of the database.

Hardware Independence: Most DBMS are available across hardware platforms and operating systems. Thus the application programs need not be changed or rewritten when the hardware platform or operating system is changed or upgraded.

The DBMS Model

The range of data structures supported and the availability of data handling language depend on the model of DBMS is based. The models are:
1) The hierarchical model.
2) The network model.
3) The relational model.

The hierarchical model: In this parent can have many children. A child cannot have more than one parent and no child can exist without its parent.

The Network model: In this there are no restrictions on the number of parents. A record type can have any number of parent and child record types. It is more complex than the hierarchical model because of links. It can represent any structure designed in hierarchical model, and so is a superset of the hierarchical model.

Relational Model: Because of lack of linkages relational model is easier to understand and implement.

SQL can be expanded as Structured Query Language. It is used to create, manipulate and delete data in a database. This language facilitates very easy database interactions. SQL statements are simple and easy to execute.

Query

Query is a statement which is used to manipulate data available in a database, granting users roles and permissions, creating new databases, creating new tables, deleting table values etc.

Creating New Tables

Using a single statement we can able to create a new table. The following query when executed creates a new table.

Syntax

Create table TABLE_NAME (VARIABLES)

Example

Create table employee (name VARCHAR (20), num NUMBER (5))

The above query creates employee table with name and num fields. It has the ability to store number of names with their employee numbers.

Inserting Values to New Table

Insert statement is used to insert a new record into the table. The Insert statement has the following syntax.

Syntax

Insert into TABLE_NAME (VALUES)

Example

Insert into employee ('xxx', 30)

Modifying Values in a Table

We can able to modify the values available in a table by using the update query. The update query has the following syntax.

Syntax

Update TABLE_NAME set VARIABLE = VALUE where CONDITION

Example

Update employee set name='xyz' where num=30

Selecting a Particular Record

A particular record can be fetched from the table. It can be evaluated by using the select query.

Syntax

Select VARIABLES from TABLE_NAME

Example

Select name, num from employee where num=30

The above query when executed checks the database for num=30 if the condition is satisfied then the particular query will be fetched from the table.

Deleting a Record from the Table

A particular record can be deleted from the table using the following query. Using the delete statement we can able to delete a single record or a group of record having a same attribute.

Syntax

Delete from TABLE_NAME where CONDITION

Example

Delete from employee where num=30

This article describes the basic SQL operations such as creating a table, selecting values from the table, inserting a new record, modifying an existing record, deleting a record. All these queries form the basis for the database operations.

PHP scripts embedded into the HTML codes. When someone checks the source code of the page, he doesn't get anything about PHP scripts. He only learns the HTML part of coding. This article is an introduction to PHP scripting language. One can find useful information and have enough knowledge after reading my PHP tutorial series. Without delay, I would like to start my script creation step by step.

Before starting, you should know that you have to set up PHP installations into your computer. Such installations are PHP itself, Phpmyadmin interface, Mysql database and Apache Server. Without these, your scripts are nothing since they are not compiled by your computer.

Step 1:

To write PHP scripts, we need a place. That place is a simple text editor. You are free to choose your text editor. I prefer to use Notepad. There are other alternatives like Editplus or Elfima. If your operating system is not Windows but Linux, you can place your code into Vi editor or Pico. I have never tried but I think you can use Dreamveawer, Hotdog or Frontpage.

In your text editor, open an new file and save it as “myfirst.php” or “myfirst.html” . I choose the name of file as “myfirst” but you are free to choose anything.

Step 2:

I have told you before that PHP scripts are embedded into HTML codes. So, first of all we will write HTML codes. The basic codes are HTML, TITLE, HEAD and BODY. It can be written by lowercase letters as well. This part is not important.

Step 3: All PHP scripts start with “ ”. Only the part between two is compiled by server and evaluated as output.To print characters on the screen, standart output functions of PHP are used. Those are “ print ” and “ echo ”. In some circumstances, ” printf ” is used too.

The format of printing:

1. print ( “ your output ” ) ;
2. print “ your output ” ;
3. echo “ your output ” ;
4. echo ( “ your outpu t” ) ;

If you don't use any white space characters, the strings are written without any separation. For example if you write this code;

• Print ( “ Word1 ” ) ;
• Print ( “ Word2 ” ) ;

It will be outputted as Word1Word2.

To remove this fault,white space characters are used. In PHP code part, between print functions, we place < br > code.

Example: To be more understandable,I will write a simple PHP script. Output will be the name of my favorite Triond writers.

To see the output, Click here.

Open Microsoft Access.

On the task pane on the right-hand side of your Access screen, click on Blank Database

IMPORTANT: You must enter your own filename. If you do not, the program will give its own default name - it will start with db1 and next to db2 and so on. Obviously these file names mean nothing, so make sure that you give your database a name that makes sense.

Select the Create button.

You have now created your database but at the moment it has no structure and contains no data. To set up these things we must create a table.

CREATING A TABLE

Designing The Table's Structure

Before we can enter data into a database, we must:

1. Create a TABLE to take the data.
2. Define the table's FIELDS - that is, the categories of information required in this database.
3. Give each field a FIELD NAME.
4. Specify the DATA TYPE that we wish to enter into each field (i.e. whether the data in that field will be Text, Number, Currency, Date/Time etc.)

• It is best to do this planning on paper before you start putting it into Access. In this handbook the planning has already been done for you and you just have to follow the instructions.

The Database Window

The Database Window contains a number of options for different sorts of database OBJECTS: Tables, Queries, Forms, Reports and more.

To design a table we need to select Tables as above. There are three different ways of creating a new table. We are going to use Create table in Design view so click on it.

The Design view appears as a blank grid with 3 columns headed Field Name, Data Type and Description. You are going to use this grid to design your table.

Enter the word TITLE as the first Field Name in the top left-hand corner. Press the Tab key to move to the second column and note that the Data Type “Text” appears. Don't change this because the data type of film titles must be Text.

Press the Tab key twice to move down to the beginning of the second row.

Enter the next Field Name, which is STAR. Press the Tab key to move to the second column and again leave the Data Type “Text”.

Continue the same process by copying the details in the following Table Design box until you can see all five field names and their data type “Text”.

Check your own version against this next graphic:

Changing Data Types

Some of the above fields are of the wrong Data Type so we must make some changes.

First of all, we must change the Data Type of the DATE field. We shall put numeric data into that field (e.g. 1941 or 1980) so the Data Type must be NUMBER.

Click in the Data Type column in the same row as the field name DATE. Then click on the drop-down button which has appeared in the right-hand side of the box.

This produces a list of possible data types.

Now change the Data Type of the PRICE field to Currency in the same way and press the Tab key twice.

We could also use the Description column to include information about the fields that would be helpful to the persons who use the database. However, in this exercise we shall leave that column blank.

Saving The Table

As with other Microsoft programs, go to File and Save. Change the table name in the Save As box by calling it Table 1 followed by your name. For example, my table would now be called Table 1 - Ray.

Click OK. This will produce a Dialogue Box asking you whether you want to create a PRIMARY KEY.

Click on NO because at the moment it isn't necessary to use a primary key.

Closing The Table

The table has been saved and you have completed the design of a simple database.

Close the table by clicking on the X in the top right-hand corner of the design box.

This takes you back to the Database Window - close it in the same way.

NEXT: ENTERING DATA

• You already have experience of using a version of Microsoft Windows.
  
• You have previously used another software package e.g. Word or Excel.

What Is A Database?

An electronic database is a collection of data (information) on a particular topic. It is organised and stored in such a way that data can be retrieved easily and quickly when we ask the database specific questions. As you will see later, it is much more flexible than “manual” systems which use paper and filing cabinets.

Here are a few examples of database topics:

• Personal and financial details of bank customers
  
• The stock held by a supermarket
• Details of sports club members
• A catalogue of all your music CDs

When we create a new database, it is very important to plan carefully the structure that we need to suit the kind of data we want to store and retrieve.

We shall design our database as a TABLE. Before we do so, you should know the following terms:

DATABASE

A collection of data on one particular topic.

e.g. a database in a video shop, giving details of all the videos available for sale.

Each new database must have its own unique FILENAME.

TABLE

A grid consisting of columns and rows.

Each Table must have its own TABLE NAME.

One database may have a number of tables in it.

A table is often referred to by professional database designers as an ENTITY. Although these pages will still refer to tables as tables, be aware of this term!

FIELD

A column of the Table, containing one category of data.

For example, it can be the Title of a film or the name of its Director or the Price of the video.

Each field must have a heading called a FIELD NAME.

A Table may have any number of Fields.

DATA TYPE

The kind of Data contained in a Field.

The data types may be Text, Number or Currency.

Each Field must contain a single Data Type.

RECORD

A row of the Table, in which we put the actual data

For example, this could be the details of one film in all the Fields (columns)

Each record is unique. Although some records may have some data in common (e.g. two films may have the same Director), no two records will have the same data in all fields.

Finding the Oldest

Let's say that transaction T1 is accessing a relation Employee(eid, name, seclevel, age), and is looking for the oldest employee with security level (seclevel) of 1, and the oldest employee with seclevel = 2. We assume that the DBMS is using page locking. T1 identifies and locks all pages containing entries where seclevel = 1, the oldest of which is 46.

At this point, a transaction T2 starts executing an update, as a new employee has been hired. It creates an exclusive lock on a page that T1 is not reading, as it doesn't contain any entries with seclevel = 1. Then it inserts the new entry, where the age is 54. Then it removes the oldest employee with seclevel = 2, whose age is 65, and commits.

At this point, T1 locks all pages containing entries with seclevel = 2, finds the oldest, who is 62, and returns the results.

What happened here?

The result of these interleaved operations is that T1 returns the ages 46 and 62. If the transactions had been executed serially, the result would have been either 46 and 65 (T1 first, then T2) or 54 and 62 (T2 first, then T1).

As you can see, the result is not identical to any serial execution of the two transactions, which a robust DBMS should guarantee.

A Solution

To find a cure for this oddity, we can take one of two approaches, depending of the structure of the DB.

• If the DB has an index on the seclevel field, T1 must identify the page containing the index, and lock the ones that contain an entry with seclevel = 1. If no such entry exists, it must lock the page where there would be an entry with seclevel = 1, if one were created. This is called index locking, and prevents creation of a new entry, as the index page for the new entry is locked.
• If there is no index on the seclevel field, T1 must lock all existing pages for reading, as well as ensuring that no new page can be created. Thus no new entry can be created.

Both techniques ensures no modification of existing entries with seclevel = 1, and that no new entry of this type can be inserted.

Conclusion

The two possible solutions available for this issue generates new requirements for the DBMS. For instance, the DBMS must let T1 be able to identify the correct index page to lock, and it must let T1 prevent creation of new pages.

This only demonstrates a tiny piece of the massive complexity of robust and consistent DBMS.

First Normal Form

A relation is said to be in First Normal Form (1NF) if and only if each attribute of the relation is atomic. This means that each columns (visualizing the relation as a table) only contains a single value and that each row contains the same columns.

Violation of 1NF is commonly achieved by storing lists of data in the same field (a comma-separated list for example), which will make it difficult to write queries comparing the separate elements in the list to other entries' elements.

Id	Indexes
0	0,4,6
1	3,4

Also, by storing the list as separate fields containing the same data, there is also a breach of 1NF.

Id	Index (0)	Index(1)	Index(2)
0	0	4	6
1	3	4

The desired solution in this case would be:

Id	Index
0	0
0	4
0	6
1	3
1	4

Second Normal Form

A 1NF relation is in Second Normal Form (2NF) if and only if all non-key attributes are dependent on the whole of the candidate key, and not just a part of it.

A side-effect of this definition is that all relations in 1NF that has no composite candidate keys are automatically in 2NF.

Given the following relation:

Id	Interest	Age
0	Music	18
0	Football	18
1	Stamps	46

As you can see, the non-key attribute Age is dependent only on part of the key, namely Id, therefore, this relation is not 2NF. A conversion to make it 2NF is as follows:

Id	Interest
0	Music
0	Football
1	Stamps

Id	Age
0	18
1	46

An Alternative Notation

To make all this easier to work with, we will introduce a more clear notation that gives us only the necessary information. In the case above we could have written it as follows:

R(ABC), F = { A->C, A->B, B->A }, where A = Id, B = Interest and C = Age. One could use the actual names instead of letters, but I find single letters easier to read and understand.

R(ABC) denotes the relation and its attributes, and F denotes the dependencies, where C depends on A, and A and B depends on each other. Analyzing the dependencies will give us the candidate keys, in this case it's only AB, as both A and B is needed to fully identify the entry, but C is not needed, as no other attribute depends on it (note that a different attribute must imply C, or C would be needed as well).

Transforming these into 2NF results in the following:

R(AB), F = { A->B, B->A }, which gives the key AB.

R(AC), F = { A->C }, which gives the key A.

Third Normal Form

A 2NF relation is in Third Normal Form (3NF) if and only if for all the non-trivial functional dependencies X->A, one of the following is true:

• X is a superkey (X identifies the whole entry unambiguously)
• A is a prime attribute (i.e. A is part of a candidate key)

To violate 3NF, consider the following relation:

R(ABCD), F = { AB->CD, C->D }, where the key would be AB.

This is a breach of 3NF, as the non-prime attribute D can be identified by C, which is not a superkey. A decomposed relation in 3NF would be:

• R(ABC), F = { AB->C }, where the key would be AB.
• R(CD), F = { C->D }, where the key would be C.

Most relations in 3NF are not affected by update, insertion and deletion anomalies, such as updating of redundant data. For instance, if we look at the non-3NF example, when D changes, the CD-pair can be represented multiple places, and unless all of them are updated correctly, an inconsistency will occur. In the 3NF version, this problem will not occur, as the data is only present at one place.

Boyce-Codd Normal Form

This is a slightly more strict version of 3NF, and imposes just one other requirement. Namely, for every non-trivial functional dependency X->A, X is a candidate key.

An example of a 3NF relation that's not BCNF could be the following (using a table to suggest the actual relations):

A=EmployeeID

B=EmployeeSSN

C=CustomerID

D=CustomerSSN

A and B are related, and C and D are related. The relation is supposed to model which employee handles which customer in a company. Since both the IDs and the Social Security Numbers are valid identifiers of both customer and employee, there is an implication between all single attributes, as follows:

R(ABCD), F = { A->B, B->A, C->D, D->C, A->C, C->A, B->D, D->B, etc.}

But the candidate keys are always two of the attributes (one that identifies the employee, and one that identifies the customer).

As you can see, the there are dependencies on attributes that are not the whole candidate key, therefore it violates BCNF.

Conclusion

There are even higher normal forms in use (4NF and 5NF), but I will not cover them here. I'm certain a search on Google will give you some information.

Using normal forms is a very efficient and safe way to model and decompose relations that are not prone to update errors and inconsistencies, and an understanding of these are very beneficial and can save a lot of time in error tracking.