Computer Skills

Database

Supervised By: [Link] Ahmed

 Agenda
• Introduction
• Data – Information
• Database
• Database Management Systems
• What is MS Access?
• Relational Databases: concepts
• Integrity Aspect
 Agenda
• Company Example
• Create Database
• Create Tables
• Create Relationships
• Create Quires
• Create Report
 Introduction
• Data:
• Data is raw, unorganized facts that need to be processed. Data can be something simple
and useless until it is organized.
• Example : Each student's test score is one piece of data.
• Information:
• When data is processed, organized, structured or presented in a given context so as to
make it useful, it is called information
• Example : The average score of a class or of the entire school is information that can
be derived from the given data.
 Introduction
• Database:
• Is a collection of information that is related. Access allows you to manage your
information in one database file. Within Access there are four major objects: Tables,
Queries, Forms and Reports.
• Tables store your data in your database
• Queries ask questions about information stored in your tables
• Forms allow you to view data stored in your tables
• Reports allow you to print data based on queries/tables that you have created
 Introduction
• Database Management System (DBMS) :
• Is a computer software program that is designed as the means of managing all
databases that are currently installed on a system hard drive or network.
• Database Management System (DBMS) Examples:
• Microsoft Access
• My SQL
• Microsoft SQL Server
• DB2
• Oracle Lite
 What is MS Access?
• MS Access is a relational database, meaning that data is stored in multiple
tables that are related to each other.
• Access is one of the few products originally developed by Microsoft
• Development began in the mid 1980’s
 Relational Database Concepts
• A relational database is a database that is perceived by its users as a
collection of relation variables (called tables)
• Tables can be updated by means of the relation assignment operators
(INSERT, UPDATE, DELETE).
 Relational Database Concepts
• A simple relational database – the departments and employees database is
shown in fig.1.
 Relational Database Concepts
• Primary Key: a unique identifier for the table. That is, a column (or column
combination) with the property that at any given time, no two rows of the
table contain the same value in that column (or column combination).
• The column DEPT# in table DEPT is a primary key
• Foreign Key: It is a column (or column combination) of one table T2
whose values are required to match those of the primary key of some table
T1.
• The column DEPT# in table EMP is a foreign key referencing the primary key
DEPT# of table DEPT. Foreign keys are used to represent relationships between
entities (tables)
Relational Data Structure Terminology
 Integrity Aspect
In relational databases, integrity constraints can be classified as:
Specific integrity constraints : They depend on the database application.
For example in the department and employees database, the following
integrity constraints may be required:
 employee salaries in the range 20k to 50k
 department budget in the range 1M to 15M
 Integrity Aspect
General integrity constraints: They must be satisfied by every relational
database:
 Entity Integrity rule (Integrity rule 1): No component of the primary key of a base
relation is allowed to accept null value( missed for some reason).
 Referential Integrity rule ( Integrity rule2 ): The database must not contain
unmatched foreign key values.
 Create Database
1. Start Access
2. Click on Blank desktop database
3. Under File Name type a name for the database
4. To change the location of where to store the database, click the folder icon
and select a location
5. Click Create
 Understanding Views
• There are multiple ways to view a database object.
• The two views for tables are Design View and Datasheet View.
 Design View is used to set the data types, insert or delete fields, and set the Primary
Key
 Datasheet View is used to enter and view the data for the records
• Switching Between Design View and Datasheet View: Click the View arrow
on the Home tab and click on either Datasheet View or Design View
 Create Table
• To Create a Table in Design View:
1. Click on the Create tab
2. Click on Table
3. Switch over to Design View on the Home tab
4. If prompted to save the table, enter a name and click on OK
5. Type the field names and select the appropriate data type for each field
6. Continue until all fields are added
 Create Table
• Understanding Fields and Their Data Types
• Field - an element of a table that contains a specific item of information,
such as a last name.
• Field’s Data Type - determines what kind of data the field can store.
 Create Table - Field’s Data Type
• Text Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations,
such as phone numbers or postal codes (255 characters)
• Memo Use for lengthy text and numbers, such as notes. Stores up to 63,999 characters
• Number Use for data to be included in mathematical calculations, except money
• Date/Time Use for dates and times
• Currency Use for currency values and to prevent rounding off during calculations.
• AutoNumber Use for unique sequential that are automatically inserted with a new record
• Yes/No Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off.
• OLE Object Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds,
• Hyperlink Use for hyperlinks (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location
in a file, a Web page on the World Wide Web, or a Web page on an intranet. Stores up to 2048 characters.
• Lookup Wizard Use to create a field that allows you to choose a value from another table or from a list of values using a
combo box
 Create Relationship
1. Click on the Database Tools tap
2. Click on Relationships
3. Choose the Tables
4. Add the Tables
5. Make the relationships by connecting the primary key on T1to the foreign
key on T2.
6. Check the Integrity constrains.
 Create Quires
• You use queries to view, change, and analyze data in different ways. You can
also use them as a source of records for forms and reports.
• To Create a Query:
• 1. Click on the Create tab
• 2. Click on Query Design in the Queries group
• 3. Select the table that you would like to base your query on
• 4. Click Add
 Create Quires
• 4. Click Add
• 5. Repeat steps 3 and 4 until all tables are added
• 6. Close the Show Table window
• The table(s) will now be displayed in the upper part of the Query Design
Screen by boxes containing the tables’ fields.
• 7. Double-click on the field names in the field list window which you would
like to include in the query
 Create Quires - Defining Criteria
• In order to control which records are displayed, you must define criteria in a query.
• The most common type of query is the Select Records query which will be
discussed below.
• To Define Criteria for Your Query:
1. Position your cursor in the criteria row in the field for which you wish to define
the criteria for
2. Type the criteria
3. Click the Run Query button
 Create Form
• A form is a database object that is used to enter or display data in a database.
• To Create a Form:
1. Open the table or query on which you are basing the form
2. Click on the Create tab
3. Click on Form in the Forms group
• A form is created and opens in Layout View.
 Create Form - Different Views
• Form View – this view allows you to view, create and edit records
• Layout View - this view is similar to Design View but is more visually-
oriented in that each control displays real data. As a result, this is a very
useful view for setting the size of controls, or performing many other tasks
that affect the visual appearance and usability of the form.
• Design View - this view gives you a more detailed view of the structure of
the form. You can see the header, detail, and footer sections for the form.
You cannot see the underlying data while you are making design changes.
 Create Report
• Reports can be based on tables or queries.
• To Create a Report:
1. Open the table or query on which you are basing the report
2. Click on the Create tab
3. Click on Report in the Reports group
• A report is created in Layout View.
 Create Report – Different View
1. Print Preview – allows you see what the report would look like on a printed
piece of paper
2. Report View – allows you to see the data without having to display it in
Print Preview
3. Layout View – allows you make design changes while browsing your data
4. Design View - gives you a more detailed view of the structure of your
report
 References
• [Link]
• [Link]
• [Link]