Given the table structure shown below, answer the following questions: team_cde
ID: 3876363 • Letter: G
Question
Given the table structure shown below, answer the following questions:
team_cde
team_lead_name
team_lead_phone
team_lead_address
team_income
team_lead_pay
hire_date
21-5Z
Alley C. Smith
904-338-3416
3334 Lee Rd., Gainsville, FL 37123
16833460.00
75000.00
01062015
25-2D
Jane D. Grant
615-898-9909
218 Clark Blvd., Nashville, TN 36362
12500000.00
78000.00
03012010
25-5A
George F. Dorts
615-227-1245
124 River Dr., Franklin, TN 29185
32512420.00
77000.00
12252012
25-9T
Alley C. Smith
904-338-3416
3334 Lee Rd., Gainsville, FL 37123
21563234.00
75000.00
01062015
27-4Q
George F. Dorts
615-227-1245
124 River Dr., Franklin, TN 29185
10314545.00
77000.00
12252012
29-2D
Alley C. Smith
904-338-3416
3334 Lee Rd., Gainsville, FL 37123
25559999.00
75000.00
01062015
31-7P
William K. Moor
904-445-2719
216 Morton Rd., Stetson, FL 30155
56850000.00
79000.00
11212014
1. How many tuples does the table contain? How many attributes are there per tuple?
2. What problem would you encounter if you wanted to produce a listing by team_lead last name, team_lead state they live in? How would you solve this problem by altering the table structure?
3. What data redundancies can you detect in the table? Explain why data redundancy is undesired?
4. Give an example of update anomaly, based on table above.
5. Give an example of delete anomaly, based on table above.
Question 2 (2 Points): Explain the concept of physical data independence and its importance in database systems.
Question 3 (2 Points): Describe (use good table layout format/style like those used in the lecture slides, i.e. Word table format) at least 3 tables (with some attributes) that might be used to store information in a Car Dealership system.
Question 4 (6 Points): List three applications (other than social-networking) you have used that most likely employed a database system to store data. For each application, list:
some tables (at least 3, and use good table layout format/style like those used in the lecture slides) that you think were used in the application.
some attributes for each table. Remember to apply what you have learned so far in this class.
team_cde
team_lead_name
team_lead_phone
team_lead_address
team_income
team_lead_pay
hire_date
21-5Z
Alley C. Smith
904-338-3416
3334 Lee Rd., Gainsville, FL 37123
16833460.00
75000.00
01062015
25-2D
Jane D. Grant
615-898-9909
218 Clark Blvd., Nashville, TN 36362
12500000.00
78000.00
03012010
25-5A
George F. Dorts
615-227-1245
124 River Dr., Franklin, TN 29185
32512420.00
77000.00
12252012
25-9T
Alley C. Smith
904-338-3416
3334 Lee Rd., Gainsville, FL 37123
21563234.00
75000.00
01062015
27-4Q
George F. Dorts
615-227-1245
124 River Dr., Franklin, TN 29185
10314545.00
77000.00
12252012
29-2D
Alley C. Smith
904-338-3416
3334 Lee Rd., Gainsville, FL 37123
25559999.00
75000.00
01062015
31-7P
William K. Moor
904-445-2719
216 Morton Rd., Stetson, FL 30155
56850000.00
79000.00
11212014
Explanation / Answer
QUESTION 1 :
1 . Tuple is row in the table.Number of tuples are 7 and 7 attributes are present for each tuple.
2. Problem : If i produce a listing by team_lead last name, team_lead state they live in then i will get Alley C. Smith,3334 Lee Rd., Gainsville, FL 37123 three times and am unable to identify them uniquely.
Solution : I will alter the table by adding primary key which is helpful in identifying everyone uniquely.Here,team_cde is suitable for primary key
3. Data redundancy means the same piece of data is held in two separate places. Here, George F. Dorts repeated 2 times and Alley C. Smith repeated 3 times.
Problems are : Storing values multiple times wastes space.The second problem is that when a field value changes, multiple occurrences need to be updated.The third problem occurs if we forget to change the values in any of the records. The database would then have inconsistent data.
4 . An update anomaly is a data inconsistency that results from data redundancy and a partial update. For example, each employee in a company has a team_lead_phone and team_lead_address.
If Alley C. Smith's team_lead_phone is an error it must be updated at least 2 times or there will be inconsistent data in the database. If the user performing the update does not realize the data is stored redundantly the update will not be done properly
5 . A Delete Anamoly exists when certain attributes are lost because of the deletion of other attributes. For example, consider 31-7P as the last employee to leave the office if we delete this record we will loose complete imformation about the William K. Moor.
2nd Question :
Physical data independence is the power to change the physical data without impacting the schema or logical data.Logical data is data about database, that is, it stores information about how data is managed inside.
Importance :
Physical data independence is the ability to modify the physical scheme without making it necessary to rewrite application programs.
3rd Question :
Customers :
Customers table with attribute like email_address, mobile_phone_number
Car Models :
Car_models table with model_id,manufacture_id,model_name
Cars_sold : Cars_sold_id,customer_id etc
4th Question :
1.Universities :
Students_information : student_id , Students_name , Students_course
Grades : student_id,grade
Courses_registered : student_id , course_id, course_name
2 . Bankings :
customers : cutomer_id , customer_address
accounts : account_id , customer_id
transaction : transaction_id , customer_id
3 . Shopping Mall:
Customers : customer_id , name, address
products : product_id , quantity ,manufacturing_date
product_sold : product_id , sold_quantity
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.