[DATABASE 20111, ] ASSIGNMENT MUHAMMAD SHAROWER UDDIN, TSD ID: 29001729
TABLE OF CONTENTS RELATIONAL DATABASE DESIGN REQUIREMENTS The basic steps in designing a database system:
2 2
TO DETERMINE THE PURPOSE OF THE SYSTEM
3
TO DETERMINE THE FIELDS WE NEED –
3
TO IDENTIFY FIELDS WITH UNIQUE VALUES
3
TO DETERMINE THE RELATIONSHIPS BETWEEN TABLES –
3
RELATIONSHIPS BETWEEN TABLES ARE GROUPED INTO 4 GROUPS: 4 TO REFINE THE DESIGN –
4
TO ENTER DATA AND CREATE OTHER SYSTEM OBJECTS –
4
HOW A RELATIONAL DATABASE ORGANISES ITS DATA?
5
SOME OF THE ELEMENTS OF TABLE STRUCTURE.
5
PRIMARY KEYS
6
CANDIDATE KEY:
6
COMPOUND KEY:
6
INDEXING
7
Indexing
Relational Database Design Requirements A database is a collection of information related to a particular subject or purpose (e.g. tracking customer orders or maintaining a music collection). If database isn't stored on a computer, or only parts of it are, we may be tracking information from a variety of sources that we have to coordinate and organize our self. A well-designed database is a prerequisite for fast data retrieval and updates. The basic steps in designing a database system: To determine the purpose of the system. To determine the tables we need in the system. To determine the fields we need in the tables. To identify fields with unique values. To determine the relationships between tables. To refine the design. To add data and create other system objects.
To determine the purpose of the system We need to know what information we want from the database. From that, we can determine what subjects we need to store facts about and what facts we need to store about each subject . To determine the tables It can be the trickiest step in the database design process. That is because the results you want from your database (e.g. the reports we want to print, the forms we want to use, the questions we want answered) don't necessarily provide clues about the structure of the tables that produce them. In fact, it may be better to sketch out and rework our design on paper first. When we design our tables, divide up pieces of information by keeping following fundamental design principles in mind: 1. A table should not contain duplicate information, and information should not be duplicated between tables (e.g. we can Store each customer address and phone number once, in one table). 2. When each piece of information is stored in only one table, we update it in one place. This is more efficient, and also eliminates the possibility of duplicate entries that contain different information. 3. Each table should contain information about one subject. When each table contains f acts about only one subject, we can maintain information about each subject independently from other subjects (e.g. we would store customer
addresses in a different table from the customers' orders, so that we could delete one order and still maintain the customer information).
To determine the fields we need – Each table contains information about the same subject, and each field in a table contains individual facts about the table's subject (e.g. A customer table may include company name, address, city, state, and phone number fields). When sketching out the fields for each table, we should keep following tips in mind: 1. To relate each field directly to the subject of the table. 2. Not to include derived or calculated data (data that is the result of an expression). 3. To include all the information we need. 4. To store information in its smallest logical parts (e.g. First Name and Last Name, rather than Name.)
To identify fields with unique valuesIn order to connect information stored in separate tables (e.g. To connect a customer with all the customer's orders) each table in our database must include a field or set of fields that uniquely identifies each individual record in the table. Such a field or set of fields is called a primary key.
To determine the relationships between tables – (Purpose of joining tables in a relational database) Now that we have divided our information into tables and identified primary key fields, we need a way to tell the system how to bring related information back together again in meaningful ways. To do this, we define relationships between tables. Relationship is an association between common fields (columns) in two tables. A relationship works by matching data in key fields. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. The kind of relationship that the system creates depends on how the related fields are defined. When we physically join two tables by connecting fields with like information, we create a relationship that is recognized by Access. The specified relationship is important. It tells Access how to find and display information from fields in two or more tables. The program needs to know whether to look for only one record in a table or to look for several records on the basis of the relationship.
Relationships between tables are grouped into 4 groups:
1. One-to-one (1:1) - each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes. 2. One-to-many (1:M) - is the most common type of relationship and it is used to relate one record in a table with many records in another table. In a one-tomany relationship, a record (parent) in Table A can have many matching records (children) in Table B, but a record (child) in Table B has only one matching record (parent) in Table A. This kind of relationship is created if only one of the related fields is a primary key or has a unique index. 3. Many-to-one (M:1) - is used to relate many records in a table with only one (single) record in another table. It is often called the lookup table relationship. Many-to-many (M:M) - is used to relate many records in a table with many records in another table.
To refine the design – After designing the tables, fields, and relationships we need the time to study the design and detect any flaws that might remain. It is easier to change our database design now, rather than after we have populated the tables with data or created the relevant screens.
To enter data and create other system objects – When we are satisfied that the table structures meet the design goals described here, then it's time to go ahead and add all our existing data to the tables. We can then create any queries, forms, reports, macros, and modules that we may want.
How a relational database organises its data? A relational database is a set of tables containing data fitted into predefined categories. Each table contains one or more categories in columns. Each row contains a unique instance of datafor the categories defined by the columns.
From our assignment we can say as example. The Bicycle Explore database includes a table that described a customer with columns for name, address, phone number and so forth. Another table describes trip id, trip date, bike route, difficulty level, route length, departure time, total price. A user of the database could obtain a view if the database that fitted the users need. For example Manager for The Bicycle Explore might like a view or report which trips were the most popular and for how many bookings. May be a client might want to know how many bookings is made on a specific date. While creating a relational database, we can define the domain of possible values in a data column and further and further constraints that may apply to that data value. For example, a domain of possible customers could allow up to ten possible customer but be constrained in one table to allow only three of these customer names to be specific.
Some of the elements of table structure. • • • •
The table is the basic unit of data Storage A table holds information about one "sort of thing", one entity Records (rows) - one for each example Fields (columns) - one for each category of information
An example of fields and Records Name
Student No
Jo Walton
Login ID
2341287
Ctl09
Award
First Class
Gary Harman
1298731
Ctl07
Second Class
Gareth Jenkins
4236542
Ctl08
First Class
•
The design of a table is set before any data is entered, it can be changed afterwards, but it may corrupt the data - e.g. if we changed, the name field in the table above replacing it with "given name" and "family name".
•
•
In Access, the table is designed in the Design View window, and the data can be entered using the Table view In design view, each field has a name data type and description, and at the bottom there are further attributes (which depend on the data type)
Primary Keys •
•
•
Every record in database must be unique, therefore it must have either a single field which is always different, or a combination of fields which is always different The field/s which are always going to be unique are called the primary key Choosing an appropriate primary key is very important, for instance in the student records database name, address, date of birth, or any combination of those would not be OK. The only possible candidates are the student number and the login ID
Candidate Key: The fields or groups of fields, which could function as a primary key. Generally you should choose a key made up of the smallest number of fields
Compound key: When a primary key is made up of more than one field •
Access can automatically add an auto number field to be used as a primary key. We should never need to use an auto number. The only time it is appropriate is when the only candidate keys are large compound keys
Indexing •
Indexing a field makes searches on that field faster, so if we know that we will search on that field frequently we might make it an index (e.g. if we will often want to search on login)
•
However, it takes up more space in the database - every time a new record is added, a new entry is added to the stored index. You can choose to force all indexed entries to be unique.
