• No products in the cart.

CS4: DATABASE DESIGN

This Unit explains more about Database design.

DATABASE DESIGN

Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.

Database design involves classifying data and identifying interrelationships. This theoretical representation of the data is called an ontology. The ontology is the theory behind the database’s design.

Download and install the following softwares that we shall be using while studying this course.

  1. Microsoft Office 2010 https://drive.google.com/file/d/18p9ZHoxhRRFy01BfmLemjccMiFhRowbd/view?usp=sharing
  2. Microsoft Visual Basic (portable) https://drive.google.com/file/d/1daY_ObVzFmrGoX_XRqm4Xo_gt9eLHOXb/view?usp=sharing
  3. For zipping and unzipping use this software (Winrar) https://drive.google.com/drive/folders/10QCneHc39W-xZJwyK6NvmwbcY62rajNS?usp=sharing

Determining data to be stored

In a majority of cases, a person who is doing the design of a database is a person with expertise in the area of database design, rather than expertise in the domain from which the data to be stored is drawn e.g. financial information, biological information etc.

Therefore, the data to be stored in the database must be determined in cooperation with a person who does have expertise in that domain, and who is aware of what data must be stored within the system.

How Computers Store Data - Intro to Computer Science

This process is one which is generally considered part of requirements analysis and requires skill on the part of the database designer to elicit the needed information from those with the domain knowledge.

This is because those with the necessary domain knowledge frequently cannot express clearly what their system requirements for the database are as they are unaccustomed to thinking in terms of the discrete data elements which must be stored.

Data to be stored can be determined by Requirement Specification.

A design process suggestion for Microsoft Access

  1. Determine the purpose of the database  – This helps prepare for the remaining steps.
  2. Find and organize the information required  – Gather all of the types of information to record in the database, such as product name and order number.
  3. Divide the information into tables  – Divide information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.
  4. Turn information items into columns  – Decide what information needs to be stored in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.
  5. Specify primary keys  – Choose each table’s primary key. The primary key is a column, or a set of columns, that is used to uniquely identify each row. An example might be Product ID or Order ID.
  6. Set up the table relationships  – Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
  7. Refine the design  – Analyze the design for errors. Create tables and add a few records of sample data. Check if results come from the tables as expected. Make adjustments to the design, as needed.
  8. Apply the normalization rules  – Apply the data normalization rules to see if tables are structured correctly. Make adjustments to the tables, as needed.

WORKING WITH EXAMPLES PRACTICALLY

0001427_microsoft-access-2010-intermediate_800

PRACTICAL QUESTION ONE

The table below shows medical records of a certain clinic

Capture

  • Create a database called Medical Details.
  • Design a table with appropriate data types in design view called Patients.
  • Enter the given data in the table.
  • Assign a UGX symbol to the treatment fee field and change date to medium format.
  • Create a query to display all the details of patients who come from either Jinja or Busia. Save it as Eastern Patients.
  • Create a query to display all the details that were not diagnosed with Malaria. Save it as No Malaria.
  • Create a form from the table and save it as Main form
  • If the Government pays 40% of treatment fee for all patients, create a query to calculate discounted fee. Put only Fname, DoB, Diagnosis and Treatment Fee on the display. Save it as Discounted fee.
  • Create a report to display the information in the discounted fee query. Save it as Discounted Report.
    Create a tabular form from discounted fee query showing all details and save it as Patient’s form.
  • Print your work.

PRACTICAL QUESTION TWO

The table below represents Kagali Secondary School student’s data.

Capture

  • Create a database and save it as Kagali database.                     
  • Create a table to capture the given information with appropriate data types. Save it as Kagali
  • Enter the data in the table below.
  • Change district names into uppercase
  • Generate a form to return the records of the following fields: STUDENT NUMBER, NAME, AGE and HOME DISTRICT. Save it as Student’s Form.
  • In the form,
    (i) Insert any clip art picture from the library to appears as the logo.

(ii) Include the title as KAGALI SECONDARY SCHOOL with font size 20.
(iii) Indicate the date and time it has been created.                              

  • Use a query to filter out student(s);
    (i) from Masaka district. Save it as Masaka.

(ii) with age above 18. Save it as Age.

  • Generate a report from the table. Save it as Student’s report.
  • Add your name and personal number as footer on your report.
  • Print your work.

PRACTICAL QUESTION THREE

You have been approached by the Computer Club president in your school to design for them a database. Study the information below and answer the questions that follow.

Capture

  • Create a database called Computer Club and create a table called Students Table with the appropriate datatypes and enter the data above.
  • Create the following queries whose members have;
  • Names between A-G. Name this query A-G query.
  • Code between1-10 and joined the club between 2004 and 2007. Save this query as M001-10 query.
  • Create a form from M001-10 query. Name it M001-10 Form.
  • In the form above add date and time and a header as “The Wizards Club 2020” in font size 24 and a footer of your names and reg no.
  • Create a report with the names in ascending order, tabular layout. Name it Computer Report.
  • Given that complete membership Fee is 20,000, create a query to display their balances in a new field BALANCE. Save it as Balance.
  • Create a query to filter out all those members from S6. Name it S6 ICT Query.
  • Create another query that will return their years spent in the club in a new field named YEARS. Name it Actual Years Spent.

PRACTICAL QUESTION FOUR

Create a database file called MYDATABASE – REG NO in the folder you created.

  1. Create a table in design view and assign appropriate data types. Save it as Employee Table.
  • For purposes of validation, enter a rule that will limit Amount fee to not exceed more than 91,000 and change date format to medium date.
  1. Create a form called “Employee Entry Form” and Insert the following recordsCapture
  2. Create a query to show all female employees who are programmers and name it “Programmers
  3. If the employees pay a tax of 15% of the amount, create a query to compute for tax in a new field named TAX. Save this query as Revenue Tax.
  4. Create another query from the Revenue Tax Query that will return employees net pay in a new field NET PAY. Save it Net Pay. Note net pay is (Amount – Tax).
  5. Create a form from the above query. Include date and time with a footer of your names and Reg no. on your form. Save it as Net pay Form. Add at least two command buttons.
  6. Create a query to display those with no Amount values. Save it as Valueless.
  7. Create query to display a post of Engineer, Finance or Technician. Save it as POST.
  8. Create a query returning all the post excluding the technician. Save it as Exclusion.
  9. Create another query that will filter members born during 1999 and after 2004. Yearly.
  10. Create a parameter query basing on the Post. Save it as Parameter.
  11. Create a query to calculate the age of all employees who earn a salary less than 50,000/= Save it as Age with <50,000.
  12. Create a report showing all records with Post in ascending order, tabular layout in portrait. Include your name as footer and a heading of your choice in the header. Save it as “Employee Report
  13. Using the above report add a field ACTUAL AGE and determine their actual ages.
  14. Given that the total amount to be paid is 91,000/=, calculate the balances for the members in a new field named BALANCE use a query. Save it as Balance.
  15. Create a query to filter out those members with a post ending with letters er. Name it End er.

THIS VIDEO EXPLAINS MORE ABOUT DATABASE DESIGN

Database Design Tutorial

ASSIGNMENT : CS4: Assignment 2 on Databases MARKS : 90  DURATION : 3 hours

Yaaka Digital Network ©, a Ultimate Multimedia Consult product
X