3. C OMPETENCIA SINDICAL EN LA NEGOCIACIÓN COLECTIVA
3.3. El modelo sindical español e incentivos generados para el desarrollo de
arrays.
FIGURE 5-6 Album Table in 1NF
AlbumTitle Track Artist ArtistCountry
Abby Road Here comes the sun Beatles UK Abby Road Octopus’s Garden Beatles UK
Abby Road Something Beatles UK
Blond on Blond Rainy Day Woman Bob Dylan US Blond on Blond Sad Eyed Lady of the lowlands Bob Dylan US Blond on Blond Stuck in Mobile with the Memphis
blues again
Bob Dylan US Office Dept. Type Status Title Email
314 HUM Instruction FT Professor [email protected] 124 ADM
212 IT Instruction PT Professor [email protected] 113 IT Instruction PT Professor [email protected] 114 MAT Staff FT Program Assistant,
Lab Assistant [email protected] 201 IT Exempt Dean IT [email protected] 200
It is obvious from looking at the preceding table that First Normal Form is not sufficient. Every column contains a single value, and there are no arrays or repeating groups, but there is a great deal of redundancy.
Example 2
In our spreadsheet example, the first attribute LastName/Dept. stores two different types of values, last names and department names. The attribute Title also has an issue. Lisa Brown has two titles,
“Program Assistant” and “Lab Assistant.” In First Normal Form, each row of an attribute must contain only a single value.
It may not be obvious at first why these things are a problem. Think about it from the point of view of someone querying the database. If they want to find a department’s phone number, they have to search through all the faculty and staff names to find it. They could apply various filters, such as searching for values that have no status or position, but that is not guar-anteed to return just what they want. A database user expects to be able to just ask for the department names and find them. The Title column is even more problematic for the database searcher. Suppose you want to find all the employees who have the title “Lab Assistant.”
When the attribute contains a list of values, you can’t simply search for that title. You would have to use some kind of pattern search or string function to extract the title from the list.
There is also no way to ensure consistency or data integrity when you have a list of values for an attribute.
To get the data to conform to First Normal Form, the first thing to do is to separate LastName and Dept. into two attributes. Since there is already a Dept. attribute, call the new attribute DeptName. Here is the first half of the spreadsheet with the correction:
FIGURE 5-7 Department and Name Separated
LastName FirstName DeptName Phone BuildingCode Building
Able Susan 206.555.2356 BE Broadway Edison
Admissions Admissions 206.555.1000 BE Broadway Edison
Anderson Elliot 206.555.1029 SA South Annex
Anderson Jolene 206.555.9001 SA South Annex
Bradley Lisa 206.555.2323 BE Broadway Edison
Brown Martin 206.555.1200 SA South Annex
Information Technology 206.555.1200 SA South Annex
The only way to solve this problem is to break the entity into two or more separate entities.
Job title will be a separate entity. We will also need a linking entity, since there is a many-to-many relationship between employees and job titles.
One last issue remains. As you learned in the last chapter on database design, each entity should have a primary key, an attribute that uniquely identifies each row stored in the entity. In the Tutor database and most examples, the book has used natural keys, that is, keys that arise from some combination of the natural attributes of an entity. But in this example, just to show an alternative approach, we will use surrogate keys. Each row will be assigned an arbitrary number in sequence.
Most relational database management systems have a utility to provide such keys. In SQL Server, it is
“Identity”; in Access, it is called an “autonumber.”
The overall Contact entity will have the key “ContactKey.” The new Title entity will have
“TitleKey” for a primary key and the linking entity will have a composite key consisting of
“ContactKey” and “TitleKey.”
When we are done, the data will look like this:
The next problem is more difficult. Title can have multiple values for a single employee.
One temptation is to add columns such as Title1, Title2, and Title3, but this solution generates more problems than it solves. For the vast majority of employees who have only one title, Title2 and Title3 would be always empty. Also, what if some enterprising employee were work-ing in four positions and had four titles. There would be no room for the fourth. For someone querying the database, this solution opens up even worse problems. If you were searching for all the employees who held a particular job title, you would have to query three different attributes.
Following is an ERD of our efforts so far:
FIGURE 5-8 Contact Table 1NF ContactKey LastName FirstName DeptName Phone BuildingCode
1 Able Susan 206.555.2356 BE
2 Admissions Admissions 206.555.1000 BE
3 Anderson Elliot 206.555.1029 SA
4 Anderson Jolene 206.555.9001 SA
5 Bradley Lisa 206.555.2323 BE
6 Brown Martin 206.555.1200 SA
7 Information Technology 206.555.1200 SA
FIGURE 5-9 Contact Table (cont.)
Building BuildingAddress Office Dept. Type Status Email
Broadway Edison 1700 Broadway 314 HUM Instruction FT [email protected] Broadway Edison 1700 Broadway 124 ADM
South Annex 1650 Broadway 212 IT Instruction PT [email protected] South Annex 1650 Broadway 113 IT Instruction PT [email protected] Broadway Edison 1700 Broadway 114 MAT Staff FT [email protected] South Annex 1650 Broadway 201 IT Exempt [email protected] South Annex 1650 Broadway 200
FIGURE 5-10 Title Table TitleKey TitleName
1 Professor 2 Program Assistant 3 Dean
4 Lab Assistant
FIGURE 5-11 Contact Title Table
ContactKey TitleKey
1 1
3 1
4 1
5 2
5 4
6 3
Contact PK ContactKey
LastName Firstname DeptName Phone BuildingCode BuildingName BuildingAddress Office
Dept Type Status Email
Title PK TitleKey
TitleName
ContactTitle PK,FK1 ContactKey PK,FK2 TitleKey
FIGURE 5-12 Contact ERD 1NF