SWTechs has an example worksheet (poorly designed) that contains data about employees, their dependents, and passports. They have over 100 employees, so the worksheet is just an example. After viewing the worksheet one can see that some employees have passports and some do not, and that some have dependents, and others do not. Many employees have multiple dependents, like a spouse and one or more children. The real table they have shows employee data, passport data, and dependent data in three separate worksheet tabs.
For example, to find data about an employee, a clerk uses Excel’s “Find” tool (Ctrl+F) to look for their last name and narrow down to the specific employee. The clerk does the same to find data about an employee’s passport and dependents. If they have no passport or no dependent, then there are all blanks (nulls) in the columns beside their name. If they have dependents, they are listed out across columns.
The clerk must manually update each employee’s age and years with company, each year, and do the same for the ages of all dependents. The clerk must do the same for passport status, determining if the passport is Current or Expired. Dependent data is entered across the columns, and currently
The actual employees, passports, and dependents are irrelevant. They are just included to show an example.
Things to remember;
Every table must have a Primary Key. It is best to assume it will be an Autonumber.
Attributes are either Unique, Single Valued, Multi-Valued, Composite, Derived, or Optional. Primary Keys are always Unique. Single Valued = Pick one from the list (Race, Gender). Multi-Valued = Pick one or more from the list (Certifications, Courses Completed)
Derived attributes require an attribute from which it is derived (Age requires DOB, Total Cost requires Quantity and Price).
ERDs do not show data. People do not belong in an architect’s building blueprint; data does not belong in an ERD. Creating an ERD showing actual data (Region names, Store names, Employee names, Job Titles) means you completely miss the point of an ERD.
For 1:M relationships, the Parent entity is the “1” side, the Child entity is the “M” side.
A Weak entity is a Child entity that cannot logically exist without the Parent Entity. The Weak entity always shares the Parent’s PK. Can a dependent exist without an employee? No, so it is weak. Can a flea exist without a dog? Yes, so it is not weak. Business rules and common sense determine weak entities.
Entities are nouns, where the names are all caps and singular. Attribute names are nouns where the names are singular.
Relationships are BI-DIRECTIONAL and are described with verbs (action words/phrases). EMPLOYEE (noun) Has (verb) DEPENDENT (noun); DEPENDENT (noun) Is Related To (verb) EMPLOYEE (noun).
The business rules and tasks follow.
Business Rules
An Employee can have no, or at most, one Passport, and a Passport is held by one and only one Employee. A Passport Number is unique, but both the Employee ID and Passport Number are unique.
An Employee can no or many Dependents, but a Dependent can be related to one and only one Employee.
Tasks – View the worksheet.
Using the software package ERDPlus
Identify the various entities.
Identify Weak entities (those that can’t exist without the parent tables PK.
Assign a unique attribute (PK) to each entity.
Assign all relevant attributes for each entity from those shown in the worksheet.
In the ERD, show attributes that are composite as such, not broken down into atomic attributes. For example, use (Name) instead of LastName, FirstName, MI
Identify all derived attributes for each entity.
Determine the relationship(s) among entities (parents and children); 1:1, 1:M, M:N
Determine the participation between related entities; mandatory versus optional.
Construct the complete ERD of entities and attributes reflecting primary keys, attributes, as well as relationships and participation.
Specify bi-directional business rules in the relationship diamonds.
Name the ERD SWTechs.
Use the snipping tool to take a screenshot of the ERD and save it only as a .jpg file.
Upload the file to Dropbox.
Grading Rubric
Correct image .jpg extension (I will not try to open and grade other image types)
Correct number of entities
Correct PKs assigned
Correct FK noted
Correct relationships
Correct cardinalities
Strong versus Weak entities
Last Completed Projects
| topic title | academic level | Writer | delivered |
|---|