Republic of the Philippines
TECHNOLOGICAL UNIVERSITY OF THE PHILIPPINES
College of Industrial Education
Batangas Extension Campus
Brgy. Bungahan, Cuenca, Batangas
Semi-detailed Lesson Plan
I. OBJECTIVES
At the end of the lesson, the students should be able to:
1. identify the database design and it’s process;
2. recognize the significance of having a well-designed database in different purposes;
and
3. execute the learnings gathered on making a successful and precise database design.
II. SUBJECT MATTER
A. Topic: Database Design
B. Sub-topic: Database Design Process
C. Instructional Materials: monitor, laptop, colored paper
D. References: https://support.microsoft.com/en-us/office/database-design-basics-eb2159cf-
1e30-401a-8084-bd4f9c9ca1f5
III. PROCEDURE
A. Preparation
1. Daily Routine
1.1 Class Prayer
1.2 Greetings
1.3 Checking of Attendance
1.4 Review the Past Lesson
1.5 Assignment
B. Presentation
1. Motivation:
The teacher prepared a game entitled “Pls, Pagsunod-sunurin mo ako”. The facilitator
will pick at least 3 students who will participate to the game. He/she will guess the proper
steps of the given specific topic. He/she will give 20 seconds to do the game.
2. Raised Up Questions:
What is database?
What is database design process?
What are the process of having a database design?
3. Unlocking Difficulties:
Database- it is an organized collection of structured information, or data, typically stored
electronically in a computer system
Normalization- it is the process of organizing data in a database
Design- it is the organization of data according to a database model
Redundant- things that are unnecessary or could be left out
Code- a series of letters or digits or both, sometimes including spaces or punctuation,
included in a postal address for the purpose of sorting mail.
C. Lesson Proper
1. Teacher Demonstration.
The teacher will discuss about the following:
• Database design basics
Divides your information into subject-based tables to reduce redundant data. Provides Access
with the information it requires to join the information in the tables together as needed. Helps
support and ensure the accuracy and integrity of your information. Accommodates your data
processing and reporting needs
• Some database terms to know
Access organizes data into tables, resembling spreadsheets. These tables consist of rows and
columns, each referred to as a record and a field. Each record contains a single piece of data,
while the field is an item type.
• Good database design
The database design process is guided by several principles. The first principle is that redundant
data, or duplicate information, is undesirable. The importance of information accuracy and
completeness is the second principle.
Divides your information into subject-based tables to reduce redundant data.
Provides Access with the information it requires to join the information in the tables together as
needed.
Helps support and ensure the accuracy and integrity of your information.
Accommodates your data processing and reporting needs.
THE DESIGN PROCESS
The design process consists of the following steps:
Determine the purpose of your database
Find and organize the information required
Divide the information into tables
Turn information items into columns
Specify primary keys
Set up the table relationships
Refine your design
Apply the normalization rules
DETERMINING THE PURPOSE OF YOUR DATABASE
Noting down the database’s goal, your intended use, and the users of the database is an
effective plan. Database is depending on you as a user, it can be simple and more complex.
When it comes to the business, if the business is small you can be able to use simple database,
but if many people will use the database you should make a database that is more complex and
wide. This way we can be able to identify our goals through the decisions.
FINDING AND ORGANIZING THE REQUIRED INFORMATION
You should first begin with finding the existing information. For example, record all the
purchase items of the customer. Get all information such as identifying and all the items bought,
all items have potential column in a table.
Then, don’t stress about making this list great at first as you’re putting it together. Rather,
make a list of everything that comes to minds Consult with any other users of the database like
customers to get their suggestions as well. Later on, you can refine the list.
Next, think about the kinds of mailings or reports you could want to generate using the
database. For example, you may want an inventory summary report that displays product
inventory levels or a product sales report that breaks down sales by area. You may also wish to
create form letters that provide a premium or announce a sale event to send to your customers.
Imagine how the report would appear and design it in your thoughts. Which details would you
include in the report? Enumerate every item. Apply the same logic to the form letter and any
additional reports you want to write. Giving thoughts to the reports and mailings will be the key
to identify the items in your database. Consider the following scenario: You wish to print a list of
clients who have chosen to receive periodic emails, but they can choose not to. You add a “Send
e-mail” column to the customer table in order to record such data. You can enter Yes or No in
the field for each customer.
You will also need to know the email address to which to send emails after you have
confirmation that a customer wishes to receive them. As a result, you must have each customer’s
email address on file.
It makes good sense to construct a prototype of each report or output listing and consider what
items you will need to produce the report. For instance, when you examine a form letter, a few
things might come to mind. If you want to include a proper salutation — for example, the “Mr.”,
“Mrs.” or “Ms.” String that starts a greeting, you will have to create a salutation item. Also, you
might typically start a letter with “Dear Mr. Smith”, rather than “Dear. Mr. Sylvester Smith”.
This suggests you would typically want to store the last name separate from the first name.
A key point to remember is that you should break each piece of information into its smallest
useful parts. In the case of a name, to make the last name readily available, you will break the
name into two parts — First Name and Last Name. To sort a report by last name, for example, it
helps to have the customer’s last name stored separately. In general, if you want to sort, search,
calculate, or report based on an item of information, you should put that item in its own field.
Think about the questions that you want to put in your database. How many items did you sell
every week? Where do you best customers live? What is the name of the supplier? Through we
can identify the whole process of your business both the help of the database.
After this step, you can now proceed to the next step or procedure.
DIVIDING THE INFORMATION INTO TABLES
To divide the information into tables, choose the major entities, or subjects. For example, after finding
and organizing information for a product sales database, the preliminary list might look like this:
The major entities shown here are the products, the suppliers, the customers, and the orders. Therefore,
it makes sense to start with these four tables: one for facts about products, one for facts about suppliers,
one for facts about customers, and one for facts about orders. Although this doesn’t complete the list, it is
a good starting point. You can continue refining this list until you have a well-designed design.
When you first review the preliminary list of items, you might be tempted to place them all in a table
instead of the four shown in the preceding illustration. You will learn here why that is a bad idea.
Consider for a moment, the table shown here:
In this case, each row contains information about both the product and its supplier. Because you can
have many products from the same supplier, the supplier’s name and address information has to be
repeated many times. This wastes disk space. Recording the supplier information only once in a separate
Suppliers table, and then linking that table to the Products table, is a much better solution.
A second problem with this design comes about when you need to modify information about the
supplier. For example, suppose you need to change a supplier's address. Because it appears in many
places, you might accidentally change the address in one place but forget to change it in the others.
Recording the supplier’s address in only one place solves the problem.
When you design your database, always try to record each fact just once. If you find yourself repeating
the same information in more than one place, such as the address for a particular supplier, place that
information in a separate table.
Finally, suppose there is only one product supplied by Coho Winery, and you want to delete the
product but retain the supplier name and address information.
How would you delete the product record without also losing the supplier information?
TURNING INFORMATION INTO ITEM COLUMN
To determine the columns in a table, decide what information you need to track about the subject recorded
in the table. For example, for the Customers table, Name, Address, City-State-Zip, Send e-mail,
Salutation, and E-mail address comprise a good starting list of columns. Each record in the table contains
the same set of columns, so you can store Name, Address, City-State-Zip, Send e-mail, Salutation, and E-
mail address information for each record.
Once you have determined the initial set of columns for each table, you can further refine the columns.
For example, it makes sense to store the customer name as two separate columns: first name and last
name, so that you can sort, search, and index on just those columns. Similarly, the address consists of five
separate components, address, city, state, postal code, and country/region, and it also makes sense to store
them in separate columns. If you want to perform a search, filter, or sort operation by state, for example,
you need the state information stored in a separate column.
The following list shows a few tips for determining your columns.
Don’t include calculated data
In most cases, you should not store the results of calculations in tables. Instead, you can have Access
perform the calculations when you want to see the result. However, there is no Units On Order subtotal
column in any table. Instead, the Products table includes a Units On Order column that stores the units on
order for each product. Using that data, Access calculates the subtotal each time you print the report. The
subtotal itself should not be stored in a table.
Store information in its smallest logical parts
You may be tempted to have a single field for full names, or product names along with product
descriptions. If you combine more than one kind of information in a field, it is difficult to retrieve
individual facts later.
SPECIFYING PRIMARY KEYS
Each table should include a column or set of columns that uniquely identifies each row stored in the table.
This is often a unique identification number, such as an employee ID number or a serial number. In
database terminology, this information is called the primary key of the table.
If you already have a unique identifier for a table, such as a product number that uniquely identifies each
product in your catalog, you can use that identifier as the table’s primary key — but only if the values in
this column will always be different for each record. You cannot have duplicate values in a primary key.
You should always choose a primary key whose value will not change. In a database that uses more than
one table, a table’s primary key can be used as a reference in other tables. If the primary key changes, the
change must also be applied everywhere the key is referenced. Using a primary key that will not change
reduces the chance that the primary key might become out of sync with other tables that reference it.
Often, an arbitrary unique number is used as the primary key. If you don’t have in mind a column or set of
columns that might make a good primary key, consider using a column that has the AutoNumber data
type. When you use the AutoNumber data type, Access automatically assigns a value for you. Such an
identifier is factless; it contains no factual information describing the row that it represents. Factless
identifiers are ideal for use as a primary key because they do not change. A primary key that contains
facts about a row a telephone number or a customer name, for example is more likely to change because
the factual information itself might change.
A column set to the AutoNumber data type often makes a good primary key. No two product IDs are the
same.
In some cases, you may want to use two or more fields that, together, provide the primary key of a table.
For example, an Order Details table that stores line items for orders would use two columns in its primary
key: Order ID and Product ID. When a primary key employs more than one column, it is also called a
composite key.
For the product sales database, you can create an AutoNumber column for each of the tables to serve as
the primary key: ProductID for the Products table, OrderID for the Orders table, CustomerID for the
Customers table, and SupplierID for the Suppliers table.
Creating the table relationships
• Now that you have divided your information into tables, you need a way to bring the information
together again in meaningful ways. For example, the following form includes information from several
tables.
- 1. Information in this form comes from the Customers table...
- 2. ...the Employees table...
- 3. ...the Orders table...
- 4. ...the Products table...
- 5. ...and the Order Details table.
Creating a many-to-many relationship
• A single order can include more than one product. On the other hand, a single product can appear on
many orders. Therefore, for each record in the Orders table, there can be many records in the Products
table. And for each record in the Products table, there can be many records in the Orders table. This type
of relationship is called a many-to-many relationship because for any product, there can be many orders;
and for any order, there can be many products. Note that to detect many-to-many relationships between
your tables, it is important that you consider both sides of the relationship.
In the product sales database, the Orders table and the Products table are not related to each other directly.
Instead, they are related indirectly through the Order Details table. The many-to-many relationship
between orders and products is represented in the database by using two one-to-many relationships:
The Orders table and Order Details table have a one-to-many relationship. Each order can have more
than one line item, but each line item is connected to only one order.
The Products table and Order Details table have a one-to-many relationship. Each product can have
many line items associated with it, but each line item refers to only one product.
Creating a one-to-many relationship
• To represent a one-to-many relationship in your database design, take the primary key on the "one" side
of the relationship and add it as an additional column or columns to the table on the "many" side of the
relationship. In this case, for example, you add the Supplier ID column from the Suppliers table to the
Products table. Access can then use the supplier ID number in the Products table to locate the correct
supplier for each product.
• The Supplier ID column in the Products table is called a foreign key. A foreign key is another table’s
primary key. The Supplier ID column in the Products table is a foreign key because it is also the primary
key in the Suppliers table.
Creating a one-to-one relationship
Another type is the one-to-one relationship. For instance, suppose you need to record some special
supplementary product information that you will need rarely or that only applies to a few products.
Because you don't need the information often, and because storing the information in the Products table
would result in empty space for every product to which it doesn’t apply, you place it in a separate table.
Like the Products table, you use the ProductID as the primary key.
When you detect the need for a one-to-one relationship in your database, consider whether you can put
the information from the two tables together in one table. If you don’t want to do that for some reason,
perhaps because it would result in a lot of empty space, the following list shows how you would represent
the relationship in your design:
If the two tables have the same subject, you can probably set up the relationship by using the same
primary key in both tables.
If the two tables have different subjects with different primary keys, choose one of the tables (either one)
and insert its primary key in the other table as a foreign key.
REFINING THE DESIGN
If you can use the database to get the answers you want. Create rough drafts of your forms and reports
and see if they show the data you expect. Look for unnecessary duplication of data and, when you find
any, alter your design to eliminate it.
As you try out your initial database, you will probably discover room for improvement. Here are a few
things to check for:
•Did you forget any columns? If so, does the information belong in the existing tables? If it is information
about something else, you may need to create another table.
•Are any columns unnecessary because they can be calculated from existing fields? If an information item
can be calculated from other existing columns a discounted price calculated from the retail price.
•Are you repeatedly entering duplicate information in one of your tables? If so, you probably need to
divide the table into two tables that have a one-to-many relationship.
•Do you have tables with many fields, a limited number of records, and many empty fields in individual
records? If so, think about redesigning the table so it has fewer fields and more records.
•Has each information item been broken into its smallest useful parts? If you need to report, sort, search,
or calculate on an item of information, put that item in its own column.
•Does each column contain a fact about the table's subject? If a column does not contain information
about the table's subject, it belongs in a different table.
REFINING THE PRODUCT TABLE
Suppose that each product in the product sales database falls under a general category, such as beverages,
condiments, or seafood. The Products table could include a field that shows the category of each product.
Suppose that after examining and refining the design of the database, you decide to store a description of
the category along with its name. If you add a Category Description field to the Products table, you have
to repeat each category description for each product that falls under the category this is not a good
solution.
Applying the Normalization Rules
Normalization is the process of implementing data normalization rules in your database design, which
ensures the correct construction of tables by checking for the correctness of the rules.
First Normal Form
It talks about that always intersect the rows and columns in the table, it means that there’s only one value
must be there.
Second Normal Form
It talks about that it requires more than that each non-key column be fully dependent on the entire primary
key, not on just part of the key. However this design also violates second normal form, because Product
Name is dependent on Product ID, but not on Order ID, so it is not dependent on the entire primary key.
Third normal form
• Third normal form requires that not only every non-key column be dependent on the entire primary key,
but that non-key columns be independent of each other.
• Another way of saying this is that each non-key column must be dependent on the primary key and
nothing but the primary key. For example, suppose you have a table containing the following columns
2. Student Re-demonstration
• Discuss the significance of having a well-organized database design
• Explain the steps of making database design
D. Application
In this activity, create a table that contains your family member’s basic information such as
name, gender and age. You can use excel or any spreadsheet application or software. The data
to be entered must be correct and complete in the table you created.
Rubrics:
Excellent Very good Good Fair Poor
(5) (4) (3) (2) (1)
Organized
Completeness of data
Table of database
Time
Total
Final total: /20
E. Generalization
The teacher will call at least two (2) students to generalize the topic.
Understanding databases, it explains the purpose of databases in storing and organizing
information for efficient retrieval and manipulation. Discusses the importance of understanding
the information needs of an organization or application before designing a database. Organizing
of information, it emphasizes the significance of organizing information into tables with rows
and columns to create a structured database schema. Explains the concept of relationships
between tables, including one-to-one, one-to-many, and many-to-many relationships, and how
they are represented in a database schema. Discusses the process of normalization to eliminate
data redundancy and ensure data integrity by organizing data into separate tables and defining
relationships between them. Covers methods for maintaining data integrity through constraints,
such as primary keys, foreign keys, unique constraints, and check constraints.
F. Evaluation
Direction: Read the following questions and encircle the letter of the correct answer.
1. Access organizes your information into___?
a. Tables
b. Rows
c. Columns
d. None of the above
2. What will happen if you have a bad database design?
a. All information are still organized and easy to understand
b. The database design will give you a huge problem in gathering the data
c. The database design will not change
d. All of the above
3. How you will make sure that the database design are organized?
a. Follow the proper database design process one by one
b. Follow the opinion of people who never have an experience about making
database
c. Follow your heart
d. None of the above
4. Having a well-designed database will___?
a. Easy for us to manage all information
b. Make ourselves creative and professional
c. Create a big problem
d. Both a and b
5. Why is it important to follow the database design process?
a. To achieve our goals not only for now, but also for the future
b. For nothing
c. Never mind
d. None of the above
IV. ASSIGNMENT
A. Follow-up Assignment:
Make a simple database that consist of 1 to 2 tables. It depends on you what thing you want to
record in your database. Pass it on the next meeting. Use available software or application you
have it can be through spreadsheet, excel and more.
B. Advance Assignment
Read the database normalization
Study the three form of normalization
Prepared by: Checked by:
ABANADOR, JOMAR C
VINCENT GAHUM
HIDALGO, MARK LOUIE Instructor
CABILES, RENE
TAPIRE, JOANNA MARYA
MERCADO, LYKA
BSIE-ICT 2A
