Features of Database, Slides of Database Management Systems (DBMS)

Download Features of Database and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

DATABASE DESIGN

INTRODUCTION

Unit outcomes

Understand the features of relational

databases

Be able to design, create and populate a

relational database

Be able to test a relational database

Unit introduction

 Database software is one of the most commonly used application packages in business.

Many jobs involve the use of databases and for this reason employees with database skills

are valued.

 The advantages of using a relational database are extensive, including significantly

reduced data storage requirements, improved record manipulation and faster access to

records.

 Most organisations use databases in some way to store records, for example customer

information, supplier information, employee details and financial information. These records

can be searched, sorted, ordered, and cross-referenced using relational databases. Using a

simplified chart tool, graphs and charts can also be created and embedded in reports.

 Importing and exporting data to and from databases will be practised in this unit.

 To ensure that relational databases have integrity, validity and efficiency, designing the

database prior to implementation is important. Failure to do this may result in a poor

product.

 Learners will consider the validation and verification methods that can be implemented to

ensure that the data stored in a database is as accurate as possible. Efficient relational

database design is managed through the process of normalisation and learners will be

using normalisation techniques to develop efficient and effective relationships between

entities.

Criteria & Assesments

Steps

Why do we use relational databases?

Designing a relational database

Implementing a relational database

Testing a relational database

Benefits of a database

LESSON 1:

Database Terminologies

 The central concept of a database is that of a collection of records, or pieces of knowledge.

Tables



When you create a database, you store your data in

tables — subject-based lists of rows and columns.

 A table contains data about a particular subject, such as

employees or products.



Each record in a table contains information about one

item, such as a particular employee.



A record is made up of fields , such as name, address

and telephone number. A record is also commonly called

a row , and a field is also commonly called a column.

Tables

ID Initial Surname Title Address Postcode Dog Name Gender DOB Breed 1 A Smith Mrs 4 High Street Hereford HR1 1ZX Ant M 21/08/02 Alsatian 1 A Smith Mrs 4 High Street Hereford HR1 1ZX Dec M 21/08/02 Alsatian 1 A Smith Mrs 4 High Street Hereford HR1 1ZX F 08/08/04 Terrier 1 A Smith Ms 4 High Street Hereford HR1 1ZX Rooney M 23/10/05 Poodle 2 C Miles Mr 72 Castle Road, Ledbury HR7 AA Fang M 14/03/ 3 D Jones Mr 9, Back Street, Ludlow, Shropshire SY21 BB Sammy M 18/03/ 3 D Jones Mr 9, Back Street, Ludlow, Shropshire SY21 BB Billy M 14/11/05 Spaniel 4 R Dean Dr 14 Bryngwyn, Monmouth NP7 AS Suzy F 09/03/03 Bloodhound 5 F Read Miss 18 Low Terrace, Orcop HR2 6DZ Judy F 22/10/04 Foxhound 5 F Read Miss 18 Low Terrace, Orcop HR2 6DZ Trudy F 25/11/05 Foxhound 5 F Read Miss 18 Low Terrace, Orcop HR2 6DZ Billy M 14/11/05 Spaniel 5 F Read Miss 18 Low Terrace, Orcop HR2 6D2 Jamie M 08/09/06 Dachshund 5 F Read Miss 18 Low Terrace, Orcop HR2 6DZ Tammy M 26/01/07 Dachshund

Record

or

row

Entity Column or field

Attribute

Entities

An entity is thing or object of importance

about which we will store data.

Examples are:

Car, Student, Product, Transaction, Tax

payer

An entity normally has one table in the

database

Tables

VIN Colour Engine Size Fuel Type

1M8GDM9A8KP

Blue 1499 Petrol

tblCAR Entity Attribute

Attribute

value

Data types – MS Access

 (^) 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, part numbers, or postal codes.  (^) Stores up to 255 characters. The FieldSize property controls the maximum number of characters that can be entered.  (^) Memo  (^) Use for lengthy text and numbers, such as notes or descriptions.  (^) Stores up to 65,536 characters.  (^) Number  (^) Use for data to be included in mathematical calculations, except calculations involving money (use Currency type).  (^) Stores 1, 2, 4, or 8 bytes. The FieldSize property defines the specific Number type.

Data types – MS Access

 (^) Yes/No  (^) Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null values are not allowed.  (^) Stores 1 bit.  (^) OLE Object  (^) Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE protocol.  (^) Stores up to 1 gigabyte (limited by disk space).  (^) Hyperlink  (^) Use for hyperlinks. A hyperlink can be a UNC path or a URL.  (^) Stores up to 64,000 characters

Query

A select query is a type of database object that

shows information in Datasheet view ( Datasheet

view : A window that displays data from a table,

form, query, view, or stored procedure in a row-

and-column format. In Datasheet view, you can edit

fields, add and delete data, and search for data.).

A query can get its data from one or more tables,

from existing queries, or from a combination of the

two. The tables or queries from which a query gets

its data are referred to as its record source.