country
$20 Bonus + 25% OFF
Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!

CPT140 Database Concepts For Functional Dependencies

tag 0 Download2 Pages / 359 Words tag Add in library Click this icon and make it bookmark in your library to refer it later. GOT IT
  • Course Code: CPT140
  • University: Peekay University
  • Country: India

Questions:

Question 1. Short Answer
(A)
Briefly explain each of the following database terms. Use no more than 2-3 lines for each term.
1. Deletion Anomaly
2. Candidate Key
3. Role (as used in ER diagrams)
(B)
When considering SQL queries, the term “alias“ can have two different uses/meanings. Explain both and give an example for each.

Question 2. ER
A company’s main resources are the people that work for it, the buildings they work in and the projects they undertake. A database is required to manage these resources at a high level.

• The company has many employees. Their name, employee ID, address and the account number of their bank account will be stored in the database.
• Employees work in cells (small groups of people). Some employees are cell leaders. Other employees are members of a cell. A given employee can only lead or be a member of one cell at a time.
• Information about current projects features in the database. Projects are identified by a four-digit code. They have a uration, and a budget.
• As needs require, employees are allocated to projects. There is no maximum number of employees for any project. Employees are expected to apply their skills to several projects at the same time. When an employee is allocated to a project, their start date is also recorded.
• Projects have many expenses. Claim forms are used to pay expenses. Claim forms come in a block or pad. The forms are numbered starting with 1 and going up. Each employee gets their own pad of claim forms. The important data from the claim forms is the date and the total amount. Each claim form can only be assigned to one project.
• The company has several large offices. Each location is known by its building name and has an address. Each employee is located at one office except roving sales people are not allocated to any office.
Draw an ER Diagram that reflects these requirements. You must only use the symbols used in the course.
 
Question 3. Relational Design
(A) For the relation;
R (A, B, C, D, E, F, G)
The following functional dependences hold, D → G, B
E → A
B → G, E, C
A → F, C
1. Use Inference rules to find the minimal basis and determine the key of the relation.
2. Based on this key, determine if the relation is in BCNF. Explain your answer in terms of the FDs and the key.
(B) The table below has basic information about Sales staff and their sales for a small appliance sales chain. It shows the employee, the items they sell as well as which store they work in.
SALES(Cost, Emp_Name, Emp_ID , Make, Grade , Price, Serial_No, Commission, Store _Address, Date, Store _Phone, Model)
The following functional dependencies apply and there are no redundancies present;Store_Address → Store_Phone Grade → Commission
Emp_ID → Store _Address Serial_No → Model, Make Emp_ID , Serial_No →Date, Cost Serial_No →Price Emp_ID → Grade Emp_ID → Emp_Name
1. Find the Primary Key of the SALES relation.
2. Prove the SALES relation is not in BCNF or 3NF.
3. Decompose the SALES relation into BCNF/3NF. Show the final schema in full with all primary keys and foreign keys mark appropriately.
4. Consider the FD;
Store_Phone-> Store_Address
Which relation in your decomposition would it map to? Test that relation to show that it is still in 3NF. Show your working.

 

Answer:

(A)

Considering

R (A, B, C, D, E, F, G)

D → G, B

E → A

B → G, E, C

A → F, C

  1. By using inference rules to find the minimal basis the key of the relation is D
  2. For the relation to be in BCNF the key which is D has to be in the left side for all the FDs which is not the case thus the relation Is not in BCNF 

(B)

Considering

SALES(Cost, Emp_Name, Emp_ID , Make, Grade , Price, Serial_No, Commission, Store _Address, Date, Store _Phone, Model)

Functional dependencies

Store_Address → Store_Phone

Grade → Commission

Emp_ID → Store _Address

Serial_No  → Model, Make

Emp_ID , Serial_No  →Date, Cost

Serial_No  →Price

Emp_ID → Grade

Emp_ID → Emp_Name

  1. The primary key of the sales relation is (Emp_ID,serial_NO)
  2. The sales table is not in 3NF and BCNF because;
 

3NF

The following transitive dependencies exist in the relation;

Store_Address → Store_Phone

Grade → Commission

Serial_No  → Model, Make, Price

Emp_ID → Grade, Emp_Name, Store_Address

BCNF

The relation is not in BCNF because the candidate key which is (Emp_ID, Serial-NO) should exist

On the left side of all the FDs which is not the case thus the relation is not in BCNF.

Decomposing sales to BCNF/3NF

This can be achieved by eliminating transitive dependencies;

Employee (Emp_ID, Grade, Emp_Name, Store_Address)

Primary key (Emp_ID)

Foreign key (Grade) references Grade (Grade)

Foreign key (Store_address) references store (store_Address)

Store (Store_Address, Store_Phone)

Grade (Grade, Commission)

Product (Serial_NO, Model, Make, Price)

Sale (Emp_ID, serial_NO, Date, Cost)

Primary key (Emp_ID, serial_NO)

Foreign key (Emp_ID) references Employee (Emp_ID)

Foreign key (Serial_No) references Product (Serial_NO)

  1. Considering the FD 
 

Store_Phone-> Store_Address

This FD should be mapped to the Store relation to form a composite primary key.

Store (store_phone, store_address)

Primary key ( Store_Phone, Store_Address)

OR

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2019). Database Concepts For Functional Dependencies. Retrieved from https://myassignmenthelp.com/free-samples/cpt140-database-concepts-for-functional-dependencies.

"Database Concepts For Functional Dependencies." My Assignment Help, 2019, https://myassignmenthelp.com/free-samples/cpt140-database-concepts-for-functional-dependencies.

My Assignment Help (2019) Database Concepts For Functional Dependencies [Online]. Available from: https://myassignmenthelp.com/free-samples/cpt140-database-concepts-for-functional-dependencies
[Accessed 28 May 2020].

My Assignment Help. 'Database Concepts For Functional Dependencies' (My Assignment Help, 2019) <https://myassignmenthelp.com/free-samples/cpt140-database-concepts-for-functional-dependencies> accessed 28 May 2020.

My Assignment Help. Database Concepts For Functional Dependencies [Internet]. My Assignment Help. 2019 [cited 28 May 2020]. Available from: https://myassignmenthelp.com/free-samples/cpt140-database-concepts-for-functional-dependencies.


University students looking for SWOT and PEST analysis assistance can simply receive comprehensive solutions on the go. MyAssignmenthelp.com is right here, available to help students with PESTLE and SWOT analysis of different brands such as Kia Motors, Apple, Samsung, Amazon and more. Our team of dedicated scholarly writers are well-known for delivering error-free SWOT and PESTLE analysis, much ahead of the specified deadline. So, seek our writing help online, and put your best foot towards submitting flawless case studies and term papers.

Latest Management Samples

MGT5STR Strategic Management 3

Download : 0 | Pages : 27
  • Course Code: MGT5STR
  • University: La Trobe University
  • Country: Australia

Answer: Introduction Telstra is one of the largest and leading telecommunication and information service organization, and it’s also one of the largest internet service provider and mobile operators. The company offers its services mostly at home and at a business with core access, local, mobile and internet services, as well as long-distance telephone call services around Australia (Philip, 2000). Telstra Corporation Limited is the Aust...

Read More arrow

MKT230 Market Research For Government And Local Authorities

Download : 0 | Pages : 11
  • Course Code: MKT230
  • University: Charles Sturt University
  • Country: Australia

Answer: Background to the research problem It is certain that not all tourists have the same taste in terms of the location and the quality of services. As they could belong to different regions, they could be of different age groups and earn different income as well as have different tastes and preferences to live their lives in a particular manner. As put forward by Jaafar et al., (2011) the tourists have different choices when it comes to se...

Read More arrow

HR9405 Managing People At Work For Appropriate Theories And Techniques

Download : 0 | Pages : 12
  • Course Code: HR9405
  • University: Newcastle University
  • Country: United Kingdom

Answer: Introduction Managing an organisation can be a challenging task for most managers. This is mainly because managers need to take into consideration various factors while managing an organisation. These challenges can be internal as well as external and the abilities of an individual as a manager are tested in these circumstances. For example, in terms of external issue, the managers face challenges from the business environment. The th...

Read More arrow

MRKT20052 Marketing Management And Digital Communications 2

Download : 0 | Pages : 2

Answer: Background of Ingogo Organizational analysis Ingogo offer taxi booking and payment services to their specified consumers and drivers. It also offer mobile app to their driver and business consumers . It is established in 2011 Mascot, Australia. Industry definition Organizations that offer many kinds of travel services like travel agencies, tour operators, internet reservation, and travel industry report (Ingogo, 2018)...

Read More arrow

BUSN20019 Professional Project For Incidence Of Leave Taking Among New Mothers

Download : 0 | Pages : 20
  • Course Code: BUSN20019
  • University: Central Queensland University
  • Country: Australia

Introduction Background of the Study The employee force that is existent throughout the world require leaves and holidays in order to take some rest and get their mind of the job and refresh themselves. The employees even require leaves due to health issues and therefore it is the responsibility of the management of the companies to construct plans with the help of which the employees would be receiving a definite number of leaves within a part...

Read More arrow
Next
watch

Save Time & improve Grade

Just share Requriment and get customize Solution.

question
We will use e-mail only for:

arrow Communication regarding your orders

arrow To send you invoices, and other billing info

arrow To provide you with information of offers and other benefits

1,321,585

Orders

4.9/5

Overall Rating

5,075

Experts

Our Amazing Features

delivery

On Time Delivery

Our writers make sure that all orders are submitted, prior to the deadline.

work

Plagiarism Free Work

Using reliable plagiarism detection software, Turnitin.com.We only provide customized 100 percent original papers.

time

24 X 7 Live Help

Feel free to contact our assignment writing services any time via phone, email or live chat.

subject

Services For All Subjects

Our writers can provide you professional writing assistance on any subject at any level.

price

Best Price Guarantee

Our best price guarantee ensures that the features we offer cannot be matched by any of the competitors.

Our Experts

Assignment writing guide
student rating student rating student rating student rating student rating 5/5

1592 Order Completed

96% Response Time

Jane Sima

Ph.D in Psychology with Specialization in Industrial-Organizational Psychology

Singapore, Singapore

Hire Me
Assignment writing guide
student rating student rating student rating student rating student rating 5/5

1896 Order Completed

95% Response Time

Herman Berens

MSc in Psychology

London, United Kingdom

Hire Me
Assignment writing guide
student rating student rating student rating student rating student rating 5/5

1758 Order Completed

99% Response Time

William Martinez

MS in Biology

Washington, United States

Hire Me
Assignment writing guide
student rating student rating student rating student rating student rating 4/5

2594 Order Completed

95% Response Time

Michael Johnson

Masters of MSc in Economics

Washington, United States

Hire Me

FREE Tools

plagiarism

Plagiarism Checker

Get all your documents checked for plagiarism or duplicacy with us.

essay

Essay Typer

Get different kinds of essays typed in minutes with clicks.

edit

GPA Calculator

Calculate your semester grades and cumulative GPa with our GPA Calculator.

referencing

Chemical Equation Balancer

Balance any chemical equation in minutes just by entering the formula.

calculator

Word Counter & Page Calculator

Calculate the number of words and number of pages of all your academic documents.

Refer Just 5 Friends to Earn More than $2000

Check your estimated earning as per your ability

1

1

1

Your Approx Earning

Live Review

Our Mission Client Satisfaction

i am really disapointed and i don't know how you guys became no. 1 but please guys if you eargarly want to help doing assignment then you guys have to chose the better experts. if you go through my order assignment and give 5 mins to read then you wi...

flag

User Id: 434718 - 28 May 2020

Australia

student rating student rating student rating student rating student rating

ALMOST EVERY TIME THE WORK IS BETTER N BEST, AND THE SERVICE IS REALLY GOOD N WORK IS ALWAYS DONE BEFORE TIME

flag

User Id: 419181 - 28 May 2020

Australia

student rating student rating student rating student rating student rating

Very happy with the work performed. Web development assignment well constructed. I recommend the website. You won\'t retreat it.

flag

User Id: 278516 - 28 May 2020

Australia

student rating student rating student rating student rating student rating

Excellent WORK, very informative, right to the point and comprehensive. really the assignment made by expert

flag

User Id: 377058 - 28 May 2020

Australia

student rating student rating student rating student rating student rating
callback request mobile
Have any Query?