Database Management

1
Database Management
2
What is a database?

• A collection of information related to a
  particular subject or purpose
• tracking customer orders
• maintaining a music collection

3
What is a database?

• Sometimes databases are not
• well organized
• stored completely on a computer

• You have to
• track information from a variety of sources
• coordinate and organize them yourself

4
Example case

• Assume a company that purchase products from
  suppliers and sells them to customers
• You need to
• contact your suppliers
• order goods in bulk
• whenever they reached their re-order levels in
  the inventory.

5
Example case cont

• However your data is not well organized.
• The phone numbers of your suppliers are stored in
  various locations.
• You have a card file (a pack of cards) for
  suppliers containing supplier phone numbers.

6
Example case cont

• You also have product information files in a file
  cabinet.
• The product information also contains supplier
  phone numbers
• Order information is stored in a computerized
  spreadsheet in Excel.

7
Example case cont
What is an order ?
Contains

• supplier name
• Address
• contact phone number
• Goods and their quantities ordered

• purchase price,
• required date,
• extended totals and sub totals, etc.

8
Example case cont
Disadvantage

• If a supplier's phone number changes,
• you might have to update that information in all
  the three places.

9
Example case cont
Advantage of a Database

• You only have to update that information in one
  place

• The supplier's phone number is automatically
  updated wherever you use it in the database

10
Role of Tables in a Database

• A database uses a collection of tables to store
  data.
• A table is a collection of data about a specific
  topic, such as
• employees, customers, products or suppliers.
• A separate table is used for each topic

11
Role of Tables in a Database

• Therefore, data stored in a particular table is
  not repeated in another table.
• For example,
• supplier pone numbers are stored only in the
  supplier table and
• not in any other table like Product or Order as
  in the previous case.

12
Role of Tables in a Database

• Using a separate table for each topic means that
  you store that data only once.
• This results in a more efficient database and
  fewer data-entry errors.

13
Tables for each topic

• What are the separate topic that require a
  separate table be created in the database for the
  company mentioned above?
• Suppliers
• Products
• Customers
• Orders
• Employees

Justify the requirement of each topic and its
table
14
Organization of Data as a Table
The Employee table in the company database
What else you think important to be in the above
table as data items?
15
Organization of Data as a Table

• Columns (fields) and rows (records)
• First row contains field names or data item names
• Each row describes a single instance of the
  Employee entity

What is an entity ? What it look likes?
16
Organization of Data as a Table

• Along a field ----- the same data item
• FirstName field --- the first name of every
  employee

Is there a same data format along a field?
17
Design the rest of the tables for the Company
database

• To create the supplier table
• Make a list of important data items of suppliers
• Draw the table on a paper
• Identify the primary key
• Enter three rows of data
• Similarly create the Product table and the
  Customer table

18
Record Identification in a table

• Records has to be identified separately from
  others
• Use a suitable field in the table
• Else add an extra field that contains a unique
  field value for each record

19
Record Identification in a table

• How the records are identified in the above
  Employee table?
• What is the name given to the record
  identification field?

20
Relationships among Tables

• Support in bringing the data back together to
  form various information

21
Relationships among Tables
The business database with one more table
Department
22
Relationships among Tables

• Several employees work in the same department.
  What is that?
• Who are those employees?
• What are the positions they hold in that
  department?

How did you get the answers? What is fk?
23
Relationship Types
24
Entity Relationship (ER) Diagrams
ER diagram for the entities Department and
Employee
25
ER Diagrams

• If there is another table Dependant, extend the
  ER diagram including the new table

An employee may have one or more dependants. To
track their information is important for the
better management of employee insurance and
benefits where their family members would also be
benefited
26
ER Diagrams
Add attributers to the entities. Underline the pk
attribute
27
How relationships work

• What is the pk of this table and why the EmpNo is
  repeating in some tuples?
• What is the fk that refers from Dependant to
  Employee
• What is the job of Ruchira pereras father and at
  what department he works?

28
ER diagrams

• Entity relationship diagrams (ERD) are drawn
  before designing the tables
• ERDs help design tables
• After designing the tables they will be
  implemented in a suitable database management
  system
• Draw an ERD for the above company database

29
Drawing ERs

• Using two entities draw an ER diagram to show
  students and grades they earned for subjects they
  taken. Attach few attributes for each of the
  entity
• Draw another ERD using three entities, so that
  separate entities for student personal
  information and subject information

30

• In a certain retail business customers place
  orders for products they purchase. An employee of
  the company takes and prepares orders for
  customers. The company requires keeping and
  tracking information about their employees,
  customers, orders, products and suppliers.
• i What are the different topics of the above
  scenario for which a different table is
  required?
• ii What are the attributes that are important for
  each of the entities mentioned above?

31

• iii Draw an ER diagram for the above scenario
  indicating different entities, attributes,
  primary keys, and foreign keys
• iv Prepare tables for your ER diagram and insert
  at least three rows to each
• v Suggest a suitable software to implement your
  database

32
Working with SQL
SQL
DDL
DML
To construct and alter the database
To manipulate data in the database
Insert and delete records, make new information
combining tables, update field values in a table,
etc
Define tables, drop, add, and change tables
33
Working with SQL

• End users are interested with the DML part
• Has three main key words

34
(No Transcript)
35
(No Transcript)