Info620 week 5 assignment enterprise database systems assignments

Name: Chrystelle Song                       Date: 07/05/2015

Week 5 – Assignment #5: Chapters 15-16                                                    Grade: 5% of Class Grade

CH 15: BASICS of FUNCTIONAL DEPENDENCIES AND NORMALIZATION FOR RELATIONAL DATABASES

#15.19 – Suppose we have the following requirements (all five) for a university database that is used to keep track of students’ transcripts:

(a)     The university keeps track of each student’s name (SNAME), student number (SNUM), social security number (SSSN), current address (SCADDR) and phone (SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate (BDATE), sex (SEX), class (CLASS) (freshman, sophomore, …, graduate), major department (MAJORDEPTCODE), minor department (MINORDEPTCODE) (if any), and degree program (PROG) (B.A., B.S., …, Ph.D.). Both ssn and student number have unique values for each student.

(b)     Each department is described by a name (DEPTNAME), department code (DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and college (DEPTCOLLEGE). Both name and code have unique values for each department.

(c)     Each course has a course name (CNAME), description (CDESC), code number (CNUM), number of semester hours (CREDIT), level (LEVEL), and offering department (CDEPT). The value of code number is unique for each course.

(d)     Each section has an instructor (INSTUCTORNAME), semester (SEMESTER), year (YEAR), course (SECCOURSE), and section number (SECNUM). Section numbers distinguish different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, …; up to the number of sections taught during each semester.

(e)     A grade record refers to a student (Ssn), refers to a particular section, and grade (GRADE).

Design a relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then, design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

#15.20 – What update anomalies occur in the EMP_PROJ and EMP_DEPT relations of Figure 15.3 and 15.4?

#15.21 – In what normal form is the LOTS relation schema in Figure 15.12(a) with respect to the restrictive interpretations of normal form that take only the primary key into account? Would it be in the same normal form if the general definitions of normal form were used?

#15.35 – Consider the relation:

        BOOK (Book_Name, Author, Edition, Year) with the data:

Book_Name

Author

Edition

Year

DB_fundamentals

Navathe

4

2004

DB_fundamentals

Elmasri

4

2004

DB_fundamentals

Elmasri

5

2007

DB_fundamentals

Navathe

5

2007

 

a.        Based on a common-sense understanding of the above data, what are the possible candidate keys of this relation?

b.       Does the above have one or more functional dependency (do not list FDs by applying derivation rules)? If so, what is it? Show how you will remove it by decomposition.

c.        Does the resulting relation have an MVD? If so, what is it?

d.       What will the final decomposition look like?

 

CH 16: RELATIONAL DATABASE DESIGN ALGORITHMS and FURTHER DEPENDENCIES

#16.20 – Show that the relation schemas produced by Algorithm 16.6 are in 3NF.

#16.21 – Specify a template dependency for join dependencies.

#16.22 – Specify all the inclusion dependencies for the relational schema of Figure 3.5.

 

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Paper Helper has assembled a team of highly skilled writers with diverse experience in the online writing circles. Our aim is to become a one stop shop for all your Academic/ online writing. Check out below our amazing service!

Essays

Essay Writing Services

At Paper Helper, we prioritize on all aspects that creates a good grade such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines. The principal purpose of essay writing is to present the author's evaluation concerning a singular subject about which they have made. Since Professionalism is the mother of every success, try our team of experienced writers in helping you complete your essays and other assignments.

Admissions

Admission Papers

You have been trying to join that prestigious institution you long yearned for, but the hurdle of an admission essay has become a stumbling block. We have your back, with our proven team that has gained invaluable experience over time, your chance of joining that institution is now! Just let us work on that essay.How do you write an admission essay? How do you begin the essay? For answers, try Quality Custom Writers Now!

Editing

Editing and Proofreading

Regardless of whether you're pleased with your composing abilities, it's never an impractical notion to have a second eye go through your work. The best editing services leaves no mistake untouched. We recognize the stuff needed to polish up a writing; as a component of our editing and proofreading, we'll change and refine your write up to guarantee it's amazing, and blunder free. Our group of expert editors will examine your work, giving an impeccable touch of English while ensuring your punctuation and sentence structures are top-notch.

Coursework

Technical papers

We pride ourselves in having a team of clinical writers. The stringent and rigorous vetting process ensures that only the best persons for job. We hire qualified PhD and MA writers only. We equally offer our team of writers bonuses and incentives to motivate their working spirit in terms of delivering original, unique, and informative content. They are our resources drawn from diverse fields. Therefore your technical paper is in the right hands. Every paper is assessed and only the writers with the technical know-how in that field get to work on it.

Coursework

College Essay Writing

If all along you have been looking for a trustworthy college essay service provider that provides superb academic papers at reasonable prices, then be glad that you search has ended with us. We are your best choice! Get high-quality college essay writing from our magnificent team of knowledgeable and dedicated writers right now!

Coursework

Quality Assignment/Homework Help

We give the students premium quality assignments, without alarming them with plagiarism and referencing issues. We ensure that the assignments stick to the rules given by the tutors. We are specific about the deadlines you give us. We assure you that you will get your papers well in advance, knowing that you will review and return it if there are any changes, which should be incorporated.