Designing a Databases

“A database is a collection of logically related data that can be organized and accessed quickly. The data can be stored in form of text, number or graphics and can be searched, retrieved and accessed quickly.”

Before actually creating a database, it is extremely important to design it. It involves creating a 'Conceptual model' of the database.

This can be considered to be a "blue-print", based on which the database will be created later. The model should be designed such, that the database should be able to satisfy the current as well as the future information needs of the organization in picture.

The database designer would have to bear in mind the fact that this conceptual model should not be dependent on the database model (whether hierarchical, network or relational).

In addition to this, the model should also be independent of the RDBMS being used.

While designing a database, several approaches can be taken. The one that we will be working on is referred to as the 'Entity Relationship model, or the 'E-R model'. We will be designing our database on the 'E-R model', using which an E-R diagram can be drawn, which represents the conceptual model of the database.

Database- A Collection of Interrelated Tables: A table, is a set of related records arranged in rows and columns. Table is a basic building block of the database. A database consists of several tables, each storing a set of related data. The tables in a database are interrelated.

EXAMPLE:-Consider a database that stores data related to an 'Airlines Reservation System'. The details of the passengers who have booked their tickets could be stored in the 'Reservation' table, while the details of cancellations could be stored in the 'Cancellation' table. The date and timings of the flights could be stored in yet another table. The fare list could also be stored in another table. All these tables being related to Airlines reservation could be ideally and appropriately stored in one database, say the 'Airlines' database.Consider the example of a Book library. Some of the details that would

need to be recorded are:1. Details of new books that are added to the Library.

2. Details of books that are lost or never returned to the Library.3. When a book is issued out to a member, the date, the member code and book code need to record.4. Details of members need to be maintained.5. Members who do not return books within a stipulated time are fined, and

these details are also to be noted down.6. The rate of each new book that is bought, as well as the charges for

transporting these books have to be recorded.7. The leave details of the staff have to be maintained.8. Assuming that there are three librarians who work in shifts and about four

other staff members. Their personal data has to be maintained too.

Understanding Data TypesIn Access, you use data types to specify the type of data each field can capture.

Data Types

Data Type Use NotesText Alphanumeric data.

Use for text and for numbers that are not used in mathematical calculations. Use for names,addresses, and other relativelyshort pieces of text. Can store upto 255 characters.

Memo Long text. Use for long pieces of text, such as notes and long descriptions. Can store up to 64,000 characters.

Number Numeric data. Use for numbers you want to use in mathematical calculations.

If you are workingwith currency, usethe currency type.

Date/Time Use for dates and times.

Currency Use for currency. Prevents rounding during calculation.

AutoNumber Unique sequential numbers or random numbers automatically inserted when you create arecord. Use to create a primarykey.

Yes/No Logical data. Use when only one of two values is valid. Yes/No,True/False, etc.

Hyperlink Use to store hyperlinks.

Attachment Use to store attachments.

OLE Object Use to attach an OLE object suchas a Word document, Excel spreadsheet, or PowerPoint presentation.

Specifying the Field Size:Along with the data type for a field, we can need to specify the field size

if the data type specified happens to be text or Numeric. Specifying the field size gives the maximum amount of data that can be stored in the field. In some cases field size need not to specified, e.g. Date automatically set to 8 bytes.

Setting DescriptionByte Stores numbers from 0 to 255.Integer Stores numbers from -32768 to 32767Long Integer

Default settings; Stores numbers from –2,147,483,648 to 2,147,483,647.

Single Stores Fractions with a decimal precision of 7 digits.Double Stores Fractions with a decimal precision of 15digits.

Tables In Access, data is stored in tables. A table is a set of columns and rows, with each column referred to as a

field. Each value in a field represents a single type of data. Each row of a table is referred to as a record.

Modify a TableAfter you create a table, you may need to modify it. You can delete columns, insert columns ormove columns.

Delete Columns

The Delete option permanently deletes columns and all the data contained in them. You cannot undo a column delete.To delete columns:1. Click and drag to select the columns you want to delete.2. Activate the Datasheet tab.3. Click Delete in the Fields & Columns group. A prompt appears.4. Click Yes. Access deletes the columns you selected.

Keys:When we worked with creating a table, we came across the concept of

'Primary Key'. A 'key' is a field or column in a table that is used to identify records.

Primary Key:A ‘Primary Key' is a field or a group of fields that uniquely identify

records in a table. It ensures that each record is unique. The 'Primary Key' for a table can be assigned when the table is created itself. In the 'Customer' table, for example, every customer has a customer code that can never be duplicated. In other words, the customer code is always unique. This field ensures that every record in a table is unique. Such a field is usually assigned

as the 'Primary Key'. Although, it is not mandatory that a 'Primary Key' be assigned to every table. Every table can have only ONE Primary Key.

Foreign Key:This is a field in a table that refers to a 'Primary Key' field in another

table. Consider the example of the relationship between the 'Customer' and

'OrderMaster' table.

The 'Ccode' field in the 'Customer' table is a 'Primary Key', while the 'Ccode' field in the 'OrderMaster' Ij table is not. When we set a relationship between these two fields, the 'Ccode' field in the 'OrderMaster' table is said to be a 'Foreign Key'. This field in the 'OrderMaster' table can only have values that already exist in the 'Ccode' field of the 'Customer' table. A 'Foreign Key' can therefore be defined as a copy of a primary key in another table.

The table, which has the ‘Primary Key’ field, is referred to as the 'Master' table, while the related table which has the 'foreign key' field is referred to as the 'Detail' table.

A 'Foreign Key' can have a Null Value.

Sorting, Filtering, and Creating Relationships

You can sort Access data so you can view records in the order you want to view them, and

You can filter data so you only see the records you want to see. Access data is stored in multiple tables. Relationships join tables together so you can work withthe data from

multiple tables..

Sort a TableBy sorting, you can put a column of information in alphabetical,

numerical, or date order.

You can sort in ascending order (alphabetical from A to Z, lowest number to highest number,earliest date to latest date) or descending order (alphabetical from Z to A, highest number to lowest number, latest date to earliest date).

You can also sort within a sort. For example, you can sort by state and then sort within each state by city. When sorting within a sort, performthe innermost sort first. For example, if you are sorting by state and then city, sort the city firstand then sort by state.

To add a sort:1. Click the column label for the column you want to sort.2. Activate the Home tab.3. Click the Ascending or Descending button in the Sort & Filter group. Access sorts thecolumn in ascending or descending order.

To remove a sort:1. Activate the Home tab.2. Click the Clear All Sorts button in the Sort & Filter group. Access clears all of the sorts youhave applied.

Filter a TableYou can apply a filter to see only the records you want to see. For example, perhaps your database contains students from the states of

DE, NJ, and PA and you only want to see the students from DE. You can filter your data so only DE students display.Each time you apply a filter to a column, it replaces any previous filter

you applied to that column. For example, if you apply a filter so you only see students in DE, and

later you apply a filter so you only see students in NJ, Access clears the DE filter and then applies the NJ filter.

You can apply filters to multiple columns in the same table. For example, by applying a filterfirst to the State field and then to the Last Name field, you can see all of the students in the state of DE whose last names are Adams.

To apply a filter:1. Click the column label for the column you want to filter.2. Activate the Home tab.3. Click the Filter button. A menu appears.4. Uncheck the items you do not want to appear, making sure only the items you want are checked.

5. Click OK. Access filters your data and displays the word Filtered at the bottom of the window.

To remove a filter:1. Activate the Home tab.2. Click Advanced in the Sort & Filter group. A menu appears.3. Click Clear All Filters. Access clears all the filters you have applied.

Creating the 'Relationships' between the tables:A 'Relationship' is a link that is created and maintained between two tables that enable data to be accessed from both the tables simultaneously.

Once the tables have been created, there is need to specify the relationship that exists between the tables.

This will come in handy when the user wants to retrieve information that exists in more than one table, as one unit.

Although this can be done even at a later stage, it is appropriate that we define the relationships when the tables are initially being created.

To create relationships:i. Close all tables and forms. (Right-click on the tab of any Object. A

menu appears. Click Close All.)ii. Activate the Database Tools tab.

iii. Click the Relationships button in the Show/Hide group. The Relationships window appears.

iv. Click the Show Table button in the Relationships group. The Show Table dialog box appears.

v. Activate the Tables tab if your relationships will be based on tables, activate the queries tab if your relationships will be based on queries, or activate the both tab if your relationships will bebased on both.

vi. Double-click each table or query you want to use to build a relationship. The tables appear inthe Relationships window.

vii. Click the Close button to close the Show Table dialog box.viii. Drag the Primary table’s primary key over the related table’s foreign

key. After you drag theprimary key to the related table’s box, the cursor changes to an arrow. Make sure the arrowpoints to the foreign key. The Edit Relationships Dialog box appears.

ix. Click the Enforce Referential Integrity checkbox.x. Click Create. Access creates a one-to-many relationship between the

tables.xi. Click the Save button on the Quick Access toolbar to save the

relationship.

Tip: When you create a relationship, you can view the related table as a sub-datasheetof the primary table. Open the primary table and click the plus (+) in the far left column. Theplus sign turns into a minus (-) sign. If the Insert Subdatasheet dialog box opens, click the tableyou want to view as a sub-datasheet and then click OK. Access displays the sub-datasheet eachtime you click the plus sign in the far left column. Click the minus sign to hide thesub-datasheet.

QueriesYou use queries to retrieve specific data from your database and to

answer questions about your data. For example, you can use a query to find the names of the employees in your database who live in a particular state.

Creating Microsoft Access QueriesA query can be based on tables or on other queries. To create a query, you open the tables or queries on which you are going to base your query in Query Design view, and then use the options in Design view to create your query. You then click the Run button to display theresults. You can save queries for later use.

To open tables or queries in Query Design view:1. Activate the Create tab.2. Click the Query Design button in the other group. The Show Table

dialog box appears.3. Activate the Tables tab if you want to base your query on tables,

activate the Queries tab if you want base your query on queries or activate the both tab if you want to base your query on bothtables and queries.

4. Click to choose the table or query on which you want to base your query.

5. Click Add. The table appears in the window.a. Click to choose the next table or query on which you want to base

your query.b. Continue clicking tables or queries until you have all the tables

and queries you plan to use.6. Click Close. Access changes to Query Design view.

Display All Records and All Fields

In Query Design view, each table has an option that allows you to display all of the fields and allof the records in a table. This option appears on the field line on the drop-down menu as thetable name followed by a period and an asterisk (tablename.*).To display all records and all fields:

1. Open a table or query in Query Design view.2. Click the down-arrow in the first field on the Field row and then select

the tablename.* option.The table name appears on the table line.3. Click the Run button. Access retrieves all of the fields and records for

the table and displaysthem in Datasheet view.

Change from Datasheet View to Query Design ViewAfter you run a query, you can easily change back to Query Design view and make modificationsto your query or create a new query.To change to Query Design view:

1. Activate the Home tab.2. Click the down-arrow below View in the Views group. A menu appears.3. Click Design View. Access changes to Query Design view. You can

modify your query.

Retrieve a Single ColumnYou can use an Access query to retrieve a single column of data. Instead of choosing thetablename.* option on the Field line in Query Design view, choose the name of the field youwant to retrieve.

To retrieve a single column:1. Open a table or query in Query Design view.2. Choose the field name you want to display in the field line.3. Click the Run button. Access retrieves the column you chose.

Retrieve Multiple ColumnsYou can use an Access query to retrieve multiple columns of data. On the Field line in QueryDesign view, choose the field name of each field you want to retrieve in the order you want toretrieve them.

To retrieve multiple columns:1. Open a table or query in Query Design view.2. Choose the field names you want to retrieve in the order you want to retrieve them.

3. Click the Run button. Access retrieves the columns you choose.

FormsForms give you the ability to choose the format and arrangement of

fields. You can use a form to enter, edit, and display data.

Reports Reports organize or summarize your data so you can print it or view it

on screen. You often use reports when you want to analyze your data or present your data to others.