Advanced database design pdf

Stany Nirojan. A short summary of this paper. Download Download PDF. Translate PDF. Whatever I have done is only due to such guidance and assistance and I would not forget to thank them. I respect and thank Mr. Kartheeban for giving me an opportunity to do this assignment on time, I extremely grateful to him for providing such a nice support and guidance.

I am really grateful because I managed to complete this assignment within the time given by my lecturer. This assignment cannot be completed without the effort and co- operation from my class mates. I would like to express my gratitude to my friends and respondents for support and willingness to spend some time with me.

A database system is usually regarded as the database which contains related tables of data maintained by a database management system DBMS , along with applications that provide controlled access to the database. And able to identify how we qualified in database development and how to solve the problems in easy ways and implement the solution. Finally, I have prepared the documentation to define what we achieve. It is designed and buildup with the user requirements, software requirements and hardware requirements.

A database which contains databases, tables of data and maintain by the Database Management System DBMS with applications controlled that databases. Databases are like Backend work process because the Frontend Applications are direct the databases. As a Database designer we need to consider about the software requirements and hardware requirements. Planning will make the system more accurate and we will find the new needs to fulfill the customers users.

Designing will allow to create a logical and physical design and able to identify what are additional use to make the system effectively and after that the development phase will start. A table wants a unique identification because there is no way to find a record without possible of finding more than one record.

So, primary key is a heart of a table. If you get a primary key from a table and when you put that key into another table it will be as foreign key. After that we will derive all data to that primary key located table to foreign key located table. They are: One — to — One This relationship implements exactly one entry in both tables. One to one relationship is a type of 4th Normalization. If you take doctors and patients, one doctor will treat many patients so doctor is in one and patients will act as many.

If we take patients entity and ward entity many patients admit in many wards at Norman hospital so they have a many to many relationships.

If we want to reduce the redundancy, the main is Primary Key to Foreign Key. If we assign a primary key in one table and put that same primary key as foreign key in other table then, that table will derive the table details. In the Patients entity for every patient the doctor id, name, doctor name will be repeat. No partial dependencies are allowed. A partial dependency exists when a field is fully dependent on a part of a composite primary key.

Composite primary key is a candidate key, that is when two primary keys in a table so one is primary key and another one is composite primary key. ER diagrams are frequently used during the design stage of a development process in order to identify different system elements and their relationships with each other.

ER Diagram is defined by the set of entities and an entity is represented by a set of attributes. In the Norman Hospital system, we already did the normalization up to 3rd and made a plan of 12 entities. Before that we will see about the Entity Relational Diagram Symbols and their meanings. First, we need to prepare the entity diagrams for the informations we have made. So, I have 13 entities and these entities are patient, doctor, patient appointment, ward admission, ward information, visit time, file information, house officer, pharmacy, discharge, ICU, admission counter and prescription.

M 1 Patients Consult Doctors The relationship between doctor and patients is one to many because one doctor consults many patients. Admission M 1 Register Patient Counter The relationship between admission counter and patients is many to one because many patients register in one admission counter.

M 1 Prescription Check Patient The relationship between patients and prescription is many to one because one patient will have many prescriptions. Enhanced ERDs are high-level models that represent the requirements and complexities of complex databases. A well-designed EER will help you build storage systems that are long-lasting and useful. Report About the Prototype System Prototype is used to demonstrate the system like an original view for the users.

The subtotal itself should not be stored in a table. You may be tempted to have a single field for full names, or for product names along with product descriptions.

If you combine more than one kind of information in a field, it is difficult to retrieve individual facts later. Try to break down information into logical parts; for example, create separate fields for first and last name, or for product name, category, and description. Once you have refined the data columns in each table, you are ready to choose each table's primary key.

Each table should include a column or set of columns that uniquely identifies each row stored in the table. This is often a unique identification number, such as an employee ID number or a serial number. In database terminology, this information is called the primary key of the table. Access uses primary key fields to quickly associate data from multiple tables and bring the data together for you.

You cannot have duplicate values in a primary key. You could easily have two people with the same name in the same table. A primary key must always have a value. If a column's value can become unassigned or unknown a missing value at some point, it can't be used as a component in a primary key.

You should always choose a primary key whose value will not change. If the primary key changes, the change must also be applied everywhere the key is referenced. Using a primary key that will not change reduces the chance that the primary key might become out of sync with other tables that reference it.

Often, an arbitrary unique number is used as the primary key. For example, you might assign each order a unique order number. The order number's only purpose is to identify an order. Once assigned, it never changes. When you use the AutoNumber data type, Access automatically assigns a value for you. Such an identifier is factless; it contains no factual information describing the row that it represents.

Factless identifiers are ideal for use as a primary key because they do not change. A primary key that contains facts about a row — a telephone number or a customer name, for example — is more likely to change, because the factual information itself might change. A column set to the AutoNumber data type often makes a good primary key. No two product IDs are the same.

In some cases, you may want to use two or more fields that, together, provide the primary key of a table. When a primary key employs more than one column, it is also called a composite key.

Now that you have divided your information into tables, you need a way to bring the information together again in meaningful ways. For example, the following form includes information from several tables.

Access is a relational database management system. In a relational database, you divide your information into separate, subject-based tables. You then use table relationships to bring the information together as needed. Consider this example: the Suppliers and Products tables in the product orders database. A supplier can supply any number of products. It follows that for any supplier represented in the Suppliers table, there can be many products represented in the Products table.

The relationship between the Suppliers table and the Products table is, therefore, a one-to-many relationship. To represent a one-to-many relationship in your database design, take the primary key on the "one" side of the relationship and add it as an additional column or columns to the table on the "many" side of the relationship.

In this case, for example, you add the Supplier ID column from the Suppliers table to the Products table. Access can then use the supplier ID number in the Products table to locate the correct supplier for each product.

The Supplier ID column in the Products table is called a foreign key. The Supplier ID column in the Products table is a foreign key because it is also the primary key in the Suppliers table. You provide the basis for joining related tables by establishing pairings of primary keys and foreign keys.

If you are not sure which tables should share a common column, identifying a one-to-many relationship ensures that the two tables involved will, indeed, require a shared column. A single order can include more than one product. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. And for each record in the Products table, there can be many records in the Orders table.

This type of relationship is called a many-to-many relationship because for any product, there can be many orders; and for any order, there can be many products. Note that to detect many-to-many relationships between your tables, it is important that you consider both sides of the relationship.

The subjects of the two tables — orders and products — have a many-to-many relationship. This presents a problem. To understand the problem, imagine what would happen if you tried to create the relationship between the two tables by adding the Product ID field to the Orders table.

To have more than one product per order, you need more than one record in the Orders table per order. You would be repeating order information for each row that relates to a single order — resulting in an inefficient design that could lead to inaccurate data. You run into the same problem if you put the Order ID field in the Products table — you would have more than one record in the Products table for each product. How do you solve this problem? The answer is to create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships.

You insert the primary key from each of the two tables into the third table. As a result, the third table records each occurrence or instance of the relationship.

Each record in the Order Details table represents one line item on an order. But together, the two fields always produce a unique value for each record. In the product sales database, the Orders table and the Products table are not related to each other directly. Instead, they are related indirectly through the Order Details table. The many-to-many relationship between orders and products is represented in the database by using two one-to-many relationships:.

The Orders table and Order Details table have a one-to-many relationship. Each order can have more than one line item, but each line item is connected to only one order. The Products table and Order Details table have a one-to-many relationship. Each product can have many line items associated with it, but each line item refers to only one product. From the Order Details table, you can determine all of the products on a particular order. You can also determine all of the orders for a particular product.

After incorporating the Order Details table, the list of tables and fields might look something like this:. Another type of relationship is the one-to-one relationship. For instance, suppose you need to record some special supplementary product information that you will need rarely or that only applies to a few products. Like the Products table, you use the ProductID as the primary key. The relationship between this supplemental table and the Product table is a one-to-one relationship.

For each record in the Product table, there exists a single matching record in the supplemental table. When you do identify such a relationship, both tables must share a common field. When you detect the need for a one-to-one relationship in your database, consider whether you can put the information from the two tables together in one table.

If the two tables have the same subject, you can probably set up the relationship by using the same primary key in both tables. If the two tables have different subjects with different primary keys, choose one of the tables either one and insert its primary key in the other table as a foreign key. Determining the relationships between tables helps you ensure that you have the right tables and columns. When a one-to-one or one-to-many relationship exists, the tables involved need to share a common column or columns.

When a many-to-many relationship exists, a third table is needed to represent the relationship. Once you have the tables, fields, and relationships you need, you should create and populate your tables with sample data and try working with the information: creating queries, adding new records, and so on.

Doing this helps highlight potential problems — for example, you might need to add a column that you forgot to insert during your design phase, or you may have a table that you should split into two tables to remove duplication. See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplication of data and, when you find any, alter your design to eliminate it.

As you try out your initial database, you will probably discover room for improvement. Here are a few things to check for:. Did you forget any columns? If so, does the information belong in the existing tables? If it is information about something else, you may need to create another table. Create a column for every information item you need to track. Are any columns unnecessary because they can be calculated from existing fields? If an information item can be calculated from other existing columns — a discounted price calculated from the retail price, for example — it is usually better to do just that, and avoid creating new column.

Are you repeatedly entering duplicate information in one of your tables? If so, you probably need to divide the table into two tables that have a one-to-many relationship. Do you have tables with many fields, a limited number of records, and many empty fields in individual records? If so, think about redesigning the table so it has fewer fields and more records. Has each information item been broken into its smallest useful parts?

Database Mcq question are important for technical exam and interview. Reason : In the narrative description of the database requirements, verbs tend to indicate the names of relationship types.

Answer : e Reason : Instructor — Student is the relational algebra operation that could be applied on the pair of relations Student and Instructor to obtain the above data. What is a Table in a Database?

Answer: A table is a database object used to store records in a field in the form of columns and rows that holds data. What is database testing?