Summary

• Data Modeling• SDLC• What is Data Modeling• Application Audience and Services • Entities• Attributes• Relationships • Entity Relationship Diagrams• Conceptual,Logical and Physical Design

Example - HW• Let us design a database for a Student Record

System, including information about these Entities - Students, Fees, Course, Textbooks,and Department;

• Conceptual Design:– Draw the E/R diagram for this database.– Work out the type of relationships among the entities

• Logical Design– Work out what are the attributes of each entity and name

key for each table

• Physical Design– How each data element will be defined in the DDL –

names, data types and length with any constraints

Conceptual Design : E- R model

Students

pay

Fees has Textbks Dept

has offers

CourseEnroll in

1. Conceptual Design

1. Draw the E/R diagram for this database.

2. Work out the type of relationships among the entities

Student……..Course(many to many)

Students……Fees (one to one)

Textbooks….Fees (one to one)

Department…Courses (one to many)

Course………Textbooks (one to many)

2. Logical Design

We now need to work what the attributes of each entity will be and how the entities will be linked together:

Students (Idnumber, Surname, First name, Gender, Sponsor)

Department (DepartmentID, Department name)

Courses (CourseNumber Course Title, Coordinator)

Textbooks (ISBN, Title, Author, Edition)

Fees (FeeCode, Fee Amount)

3. Physical Design

• Student– Idnumber : text(10) not null

– Surname : text(30)

– First name : text(30)

– Gender : text - Alpha(1)

– Sponsor : text(30)

Continue with the rest of the tables.

Refer to the Practical Exercise for Defining a table with its fields using the Design Form

Also show the how table linked thru their primary key fields.

Using the Database

The process if populating the database by entering data and then querying it to obtain information is known as DML.

• Populating: inserting information• File maintenance: saving, retrieving, importing and

exporting• Information Retrieval: selecting data• Reporting: creating reports• Sorting: choosing the order the data should be in.• Calculations: putting values from different fields together

to create new information.

Inserting and updating Database

• Using Special Data Entry Forms

• Must have tables

• Self promoting integrity

• Standard and much more presentable

Refer to the practical exercise.

Querying the Database

SQL is also used for querying the database which selects information according to specified criteria and can either display the information on screen or output as report.

Basic form of SELECT command is:

SELECT column1, column2, column N

FROM table

WHERE condition is satisfied;

Creating reports from database

Results of the query can be saved as a file which can be formatted and then presented as a report.

Report Generators: special tools for creating reports in most DBMS.

Types of Reports

1. Detail Reports: List detail fields

2. Summary Reports: provide information in summary form mainly for executives

3. Controlled-break reports: Data are grouped into categories

4. Exception reports: report which match some condition outside the normal limits

5. Graphs: Graphs and text

Who works with DatabaseDatabase administrators: oversee and manage the

database resources – database, DBMS, and other software etc.

Database Designers:identify data to be stored in the database and for choosing the appropriate structures to represent and store this data. Communicate with all prospective users in order to understand their requirements and to come up with a design that meets these requirements.

End Users: people whose jobs require access to the database querying, updating and generating reports. The database primarily exists for their use.

Casual end users: they access the database occasionally, requesting different information each time typically managers.

Naive end-users: Data entry, bank tellers, clerical staff who with main job function revolved constantly querying and updating the database using standard types of query and updates that have been programmed and tested.

Sophisticated end users: include engineers, scientists, business analysts, who thoroughly familiarize themselves with the facilities of the DBMS so as to meet their complex requirements

• Stand-alone users: they maintain personnel database by using ready-made program packages that provide easy-to-use menu or graphics based interfaces. Ie. Small business people looking after their accounting.

System Analysts and Application Programmers: SA Determines the requirements of end-users especially naive end-users and develop specifications for canned transactions to meet those requirements. AP implement these specifications as programs, then they test, debug, document and maintain these canned transactions.

DBMS Designers and Developers: are the people who design and implement the DBMS modules and interfaces as a software package. A DBMS is a complex software system consists of many modules ie. Queries, DDL, DML, Data Access etc. DBMS must interface with OS and other compilers for various programming languages. These personnel typically work with DBMS as ORACLE, FOXBASE, MS ACEESS, RBASE ETC.

Tool Developers: Tools are software package that facilitate database system design and use and help improve performance. Tools are optional package that are often purchase separately. Include packages for database design, performance monitoring, prototyping, graphical interfaces etc.

Operators and Maintenance Personnel:

These are the system administration personnel who are responsible for the actual running and maintenance of the hardware and software environment for the database system.

List the database personnel involved in each of the following tasks:

1. Design of the initial database: working out what is required.

2. Implementing the database design3. Testing data into the database and generating

reports4. Entering data into the database and generating

reports5. Maintaining the database, looking after the

host computer and carrying out regular backups.

Answers

1. DBA, USERS, May need system analysts

2. DBA, Database designers, may need application programmers

3. DBA, database designers, selected users

4. Users

5. Operators