Display of all employees showing social security number- Database Management System
Entity-Relationship Model and Database Design
Apply what you learned in the following exercise:
Tables in an Electronics Store
A fairly large local electronics store contracts with you to develop a database system for them. Your first task is to design a table for the store’s employees.
Even many of the most experienced designers “sketch” a design on paper before they start creating the database in electronic form.
Please “sketch” an employee table (basically what data items or columns should be included in the table) using the template below as a model.
The CEO of the store tells you that the following information must come from the table.
1. List/display of all employees showing social security number, first and last name, address, city, state and zip code, birth date, full-time or part-time status, salary (hourly rate) and date of hire.
2. The total number of employees.
3. List/display of all employees making more than a certain hourly rate, say $20 per hour.
4. List/display of all employees hired within a time period, say last year.
“Template” for Employee’s Table
EMPLOYEE TABLE
Social Security Number
The data items that belong in a table are a function of the information that has to come out of the table. Therefore, the determining factor is the information requirements that have been given to you.
This is your assignment:
Part I
1. Enter the data items in the column headings in row one that you believe should be in this table. In doing this, you are actually designing the table.
2. Paste your table into a document.
3. Go to Part II below and complete the assignments there.
HINTS for Part I:
You need not be concerned with the rows (except for row one, of course).
The first column of the table is the Primary Key (social security number). This has been filled in to give you a start.
Match what’s in your table to the information requirements to make certain all data has been included.
Totals that are derived from the table through computation do not have to be carried in the table.
Part II (Required)
Your work is so good that the electronics store contracts with you to do additional work for them. Your next task is to design two tables that relate to one another. These tables are: the Customer table and the Sales Order table.
The Customer table contains the basic data on each customer, and the primary key is the customer number. There is one row for each customer.
The Sales Order table contains the data for each sale. The primary key consists of two pieces of data: sales order number and customer number. There can be one or more sales orders for a customer, but only one customer on a sales order.
Use the templates below to design the two tables.
“Templates” for Table Design
Customer Table
Sales Order Table
Case Assignment
Entity-Relationship Modeling
1. Fill in the column headings in row one of each table with the data items you believe belong in the table.
2. Cut and paste your tables into the same document from Part I.
3. Suppose the Sales Order table also contains information for an employee who makes a sale. Draw an ERD of your complete design for the electronic store. If you have an ERD drawing tool such as Visio, you could draw the diagram using the tool and copy it into your document.
You could also draw it on paper, scan it, and insert it into your document. If you can’t do either of the above, you could write down the table schemata and describe the relationships among them.
4. Submit the document. Make certain the table title, course number, and module and Case ID are on the work you submit.
5. Describe the major points you learned in the readings especially on entity-relationship modeling and what lessons you have learned in this exercise. (at least half a page).
HINT: Search the Internet to find help either in the form of examples or similar problems or tutorials.
Assignment Expectations
• Design tables based on domain requirements.
• Represent database design using ERD.
• Communicate effectively with your audience.
Referencing materials
Module 2- Background
Entity-Relationship Model and Database Design
Data Modeling is the process of creating a data model for a given set of data, and it is a major part in software development. The way data is modeled is very important for how it can be accessed and manipulated using SQL.
The dominant design methodology for relational databases consists of three steps:
1. Identify all relevant entities and relationships, and describe them using ER diagrams.
2. Convert the ER model to a number of relation schemas.
3. Eliminate (or reduce) redundancy by splitting relations. This process is called normalization.
In most cases, an entity can be identified uniquely in its entity set by the values of its attributes. A relationship in the ER model is exactly the relation in the relational data model.
• Entity sets correspond to the domains of the values.
• Tuples correspond to connections between entities.
• Relationship instances form a table, like relation instances.
In our Books example from Module 1, books and authors are two entity sets. Each tuple (book, author) corresponds to a connection between two specific entities in the sets. Books and their author relationships form the table below.
Books
Authors
The Lost Symbol
Dan Brown
Superfreakonomics
Steven Levitt and Stephen Dubner
Lego Star Wars
Simon Beecroft
…
…
Good data modeling can be difficult because there may be many data models to choose from. The following design principles can be useful when considering a design:
• Be faithful to the specification of the application.
• Avoid duplication and other redundant information.
• The KISS principle (Keep It Simple, Stupid).
• Choose the right relationship.
• Use attributes when possible.
Read through the slides for Module 2, and make sure you understand the core concepts introduced there, mainly the entity relationship diagram and its components: entities, attributes, and relationships.
Common Database Design Mistakes
Neophyte database designers (and professionals, on occasion) are prone to several common mistakes. Here’s a checklist to ensure that you haven’t made any of the most common database design errors.
1. Spreadsheet design. If you need a spreadsheet, use a spreadsheet. Your database shouldn’t be a single table of all sorts of business data, especially if it included a number of calculated values. Combining different types of data in one table defeats the whole purpose of using a relational database. And while storing calculated values can speed up query and report performance, databases generally calculate values on the fly so the data is always as current as possible.
2. Too much data. The goal of a successfully designed database is to provide all of the information necessary for making decisions based on the data. There is an overwhelming desire, especially in neophytes, to encapsulate every possible nugget of data in the database. Too many fields in an entry form guarantee users will lose interest in filling in the data or will increase the amount of time to fill out the form. Plus this information increases the overall storage requirements for the database. Proper research can help identify what is essential, what might be useful in the future, and what is irrelevant.
3. Compound fields. Fields containing multiple discrete pieces of data lead to problems in searching, alphabetizing, and calculating those fields. It’s much harder to get a report of customers by ZIP code when that value is buried in a field with the address, city, and state. If it’s a distinct piece of data, make it a distinct field.
4. Missing keys. Every table needs some sort of key to identify individual records. Most database packages alert you if you leave one out during the design process, but if you use your own system (such as a flat file system), make sure there is a distinct and unique key for each record in each table.
5. Bad keys. A key has to be unique for each record. Existing database fields may appear to be good candidates for keys, but it is usually best to create an artificial key that is guaranteed to be unique. Phone numbers seem like a great key for personnel records until you run into people who live in the same home or who have multiple phone lines.
6. Missing relations. If two tables are supposed to be related, there must be a field that relates the two databases. A well-designed table relationship is useless if the appropriate foreign keys are not added to the related tables (or if the linking table for a many-to-many relationship is not created).
7. Unnecessary relationships. Just because every table can be linked to every other table does not mean that they have to be related. There is a temptation to relate tables that are logically unrelated just because you can.
8. Incorrect relations. Creating relationships between tables does not require changes in each table. A one-to-many relationship requires the primary key from the “one” table to be inserted as a foreign key in the “many” table. It does not need a foreign key placed in the “one” table since the relationship is already established in the “many” table. In fact, this arrangement may yield incorrect query results.
9. Duplicate field names. DBMS products prevent duplicate field names in a single table, but do not prevent duplicate names in different tables. While there is no programmatic reason to follow this practice, it becomes very difficult for humans to keep track of 15 relational tables where the primary key in each is called ID. It is much easier to write and debug queries if each field name is unique in the entire database.
10. Cryptic field and table names. Even more frustrating than duplicate names are cryptic names. There is no reason to limit the length of a field or table name, so use as descriptive a name as possible. Writing queries and debugging are much easier when the focus is the logic and not what “T1C1x” means.
11. Missing or incorrect business rules. Many businesses have strict rules that have nothing to do with program or database logic. Do not neglect these rules. The old adage of “garbage in, garbage out” applies since decisions made on incorrectly entered data may lead to erroneous query results and reporting.
12. Missing or incorrect constraints. A very easy way to ensure that data are entered correctly is to use constraints. These can be implemented as checks to see if an entered value is within an approved list or range of choices. Constraints can also be implemented as masks that require phone numbers or ZIP codes to fit a specified format.
13. Referential integrity. Data records that participate in relationships need to be checked when they are created or deleted to ensure that they are not orphans. Deleting one record usually requires the deletion of that record in linked tables. Ensuring referential integrity involves making sure that table declarations ensure the existence of the appropriate relationships and that integrity checks are triggered when records are deleted.
14. Database security. Almost all databases have methods to control access and user rights. For instance, end users of an invoice system probably should not have permission to create new tables or delete existing tables. Use the available security features to prevent unauthorized access and control permissions of various users and classes of users.
15. International issues. As business becomes more global, keep in mind that there are a number of formats for business data other than those of the United States. Most databases understand the various European and American date, currency, and address formats so think about whether your application will need to understand those as well.
Required Reading
• PowerPoint Presentation: Entity-Relationship Model and Database Design
• Allen, S. and Terry, E. (2005), Beginning Relational Data Modeling, Chapter 3 – Understanding Relational Modeling Terminology (read the remaining sections from Relationships), or from http://books.google.com/books?id=62CFtFea0NsC&printsec=frontcover&source=gbs_v2_summary_r&cad=0#v=onepage&q=&f=false.
• Allen, S. and Terry, E. (2005), Beginning Relational Data Modeling, Chapter 4: Understanding Data Modeling Methods: Graphical Syntax, or from http://books.google.com/books?id=62CFtFea0NsC&printsec=frontcover&source=gbs_v2_summary_r&cad=0#v=onepage&q=&f=false.
• Relational Database Design Requirements, http://www.databasedev.co.uk/database_design_requirements.html
• Teorey, Toby J., Lightstone, Sam, Nadeau, Tom. (2006). Database Modeling and Design : Logical Design. Retrieved from Trident Online Library.
