|Published (Last):||9 January 2016|
|PDF File Size:||17.17 Mb|
|ePub File Size:||13.41 Mb|
|Price:||Free* [*Free Regsitration Required]|
Published on Jan 18, SlideShare Explore Search You. Submit Search. Successfully reported this slideshow. We use your LinkedIn profile and activity data to personalize ads and to show you more relevant ads. You can change your ad preferences anytime. Upcoming SlideShare. Like this document? Why not share! Embed Size px. Start on. Show related SlideShares at end. WordPress Shortcode. Published in: Education. Full Name Comment goes here. Are you sure you want to Yes No.
No Downloads. Views Total views. Actions Shares. Embeds 0 No embeds. No notes for slide. Publishing as Pearson Addison-Wesley. Answers: a No constraint violations. FARES are kept for each flight. This assumes that the reservation has only one seat. More complex operations will be needed for a more realistic reservation that may reserve several seats at once. Give what you think should be various candidate keys and write in your own words under what constraints each candidate key would be valid.
For example, if the sections of a particular course during a particular semester are numbered 1, 2, 3, Assume that an order can be shipped from several warehouses. Specify the foreign keys for this schema, stating any assumptions you make. What other constraints can you think of for this database?
Answer: Strictly speaking, a foreign key is a set of attributes, but when that set contains only one attribute, then that attribute itself is often informally called a foreign key.
The schema of this question has the following five foreign keys: 1. We now give the queries in relational algebra: 5. Answer: The schema of this question has the following two foreign keys: 1. We now give the queries in relational algebra: 3. Answer: The schema of this question has the following four foreign keys: 3.
However, Social Security Number is usually stored in one attribute. The decision is usually based on how the database will be used. This exercise asks you to think about specific situations where dividing the SSN is useful. Answer: a. We need the area code also know as city code in some countries and perhaps the country code for dialing international phone numbers. I would recommend storing the numbers in a separate attribute as they have their own independent existence. For example, if an area code region were split into two regions, it would change the area code associated with certain numbers, and having area code in a separate attribute will make it is easier to update the area code attribute by itself.
Formatted: Bullets and Numbering 7. In general, if the each attribute has an independent logical existence based on the application, it would make sense to store it in a separate column otherwise there is no clear advantage. For example, SSN need not be split into its component unless we are using the subsequences to make deductions about validity, geography, etc. In the two cases above, it made logical and business sense to split the attributes. Note that the cell phone may be from a different city and state or province from the local phone.
Identify the critical missing information from the LocalPhone and CellPhone attributes as shown in the example above.
Hint: How do call someone who lives in a different state or province? Consider the Name attribute. What are the advantages and disadvantages of splitting this field from one attribute into three attributes first name, middle name, and last name?
What general guideline would you recommend for deciding when to store information in a single attribute and when to split the information. A combination of first name, last name, and home phone may address the issue assuming that there are no two students with identical names sharing a home phone line.
It also assumes that every student has a home phone number. Another solution may be to use first name, last name, and home zip code. This again has a potential for duplicates, which would be very rare within one university. An extreme solution is to use a combination of characters from last name, major, house number etc. If we use name in a primary key and the name changes then the primary key changes. Changing the primary key is acceptable but can be inefficient as any references to this key in the database need to be appropriately updated, and that can take a long time in a large database.
Also, the new primary key must remain unique. The challenge of choosing an invariant primary key from the natural data items leads to the concept of generated keys, also known as surrogate keys. Specifically, we can use surrogate keys instead of keys that occur naturally in the database. Some database professionals believe that it is best to use keys that are uniquely generated by the database, for example each row may have a primary key that is generated in the sequence of creation of rows tuples.
There are many advantages and disadvantages that are often been argued in design sessions. The main advantage is that it gives us an invariant key without any worries about choosing a unique primary key. The main disadvantages of surrogate keys are that they do not have a business meaning making some aspects of database management challenging and that they are slightly less efficient because they require another pass when inserting a row because the key often needs to be returned to the application after a row is 8.
Some database designers are reluctant to use generated keys also known as surrogate keys for primary keys such as StudentID because they are artificial.
Suppose that you were able to guarantee uniqueness of a natural key that included last name. Are you guaranteed that the last name will not change during the lifetime of the database? If the last name last name can change, what solutions can you propose for creating a primary key that still includes last name but remains unique?
What are the advantages and disadvantages of using generated surrogate keys? By keeping the name attributes separated, we allow the possibility of looking these pieces of their name. In a practical use, it is not likely that the user will know the correct primary key for a given student and so we must consider how a user will locate the correct row without this information.
Therefore, a practical system should allow name searches by FirstName and LastName; we must leave MiddleInitial separated still to avoid ambiguities from combining these pieces together. A single attribute Phone would no longer suffice if a student were able to have multiple phone numbers. We could possibly have multiple rows for a single student to allow this to happen, but then we have violated key principles of database design e.
A better solution would be to include the additional attributes HomePhone, CellPhone, and OfficePhone and allow the possibility of these attributes to have no value. This new relationship would allow the one-to-many relationship from students to phone numbers without creating redundant data or wasting space on sparse, valueless attributes.
You just clipped your first slide! Clipping is a handy way to collect important slides you want to go back to later. Now customize the name of a clipboard to store your clips. Visibility Others can see my Clipboard. Cancel Save.
Instructor Solutions Manual for Fundamentals of Database Systems, 6th Edition
Download instructor resources. Additional order info. Pearson offers special pricing when you package your text with other student resources. If you're interested in creating a cost-saving package for your students, contact your Pearson rep. We're sorry! We don't recognize your username or password. Please try again.