Extracts from this document...

Introduction

Database Design P1, M1 Unit 18 ? Database Design ________________ Contents Understanding the features of a database???????????????.????????2 Relational databases?????????????????????????????.????2 ? 4 Primary and Foreign Keys??????????????????????????????3 ? 4 Referential integrity???????????????????????????????????..4 Types of relationships????????????????..??????????????????5 Understanding the features of Databases A database is basically a collection of data that can include information on people for example names, telephone number or address. It could also include information on things such as the quantities of something, the status or location. Normally a database is organised by fields, records and files. A field is a column; a record is a row; and a file is a complete set of records. A good example of this is a telephone book apart not being a file it does contain a list of records with each normally or at least commonly consisting of three fields which are the name, address and phone number. A second example of a database or at least what can be used to make a database is the software Microsoft Excel, It contains columns and rows which make it a good choice for making a simple database. Below is an example of a Microsoft Excel database ? This is a database that shows students that are enrolled at a collage. There are many forms of databases the two examples above are two of the more well-known ones. ...read more.

Middle

Tables - The tables in a relational database is set out in rows and columns. For an example of a table, the image above shows how the table is set out with the fields, rows and colons. A table is also just another term for relations. Primary Key and Foreign Key The primary key is what sets up the relationships within the database. The primary key must be unique so the attribute must not repeat which means it will normally be a number of some kind for example if the Table was to keep a record for students the student ID would be used as no student will have the same ID as another student. The primary key defines the relationship with multiple tables by becoming a foreign key in the next table so they link, the foreign key will remain the primary key in the first table. I will use the same example as above to show primary and foreign keys. This is a one-to-many relationship Here you can see the Primary keys in bold are ?Customer ID? for the customers table, ?OrderID? for the orders table and for the order details table the key is ?ProductID? For the relationship to work the primary key must be in the next table and be a foreign key, this is what gives the database its referential integrity. ...read more.

Conclusion

A one-to-many relationship is created if only one of the columns is a primary key. Many-To-Many Relationships ? In a many-to-many relationship a row in ?Table A? will have more than one matching row in ?Table B? and the same will be said the one other way round as in ?Table B? will have more than one matching row in ?Table A?. This is possible by creating a third table called a junction table where the primary key consists of the foreign keys from the first two tables. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the author?s table?s primary key column and the Titles table?s primary key One-To-One Relationships ? In a one-to-one relationship a row in ?Table A? will only have one matching row in ?Table B?. A one-to-one relationship happens is both of the related columns are primary keys. This is the relationship that gets used the least because most information that is related with the same primary key would most commonly be in the same table. Some of the reasons a one-to-one relationship is to be able to divide a table with many columns or store data that is not going to be used for long and can deleted easily. Benjamin Milner ...read more.

This student written piece of work is one of many that can be found in our AS and A Level Information Systems and Communication section.