Reference no: EM13333165
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.
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?
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?
|
Prepare the standard cost sheet for the company
: Prepare the standard cost sheet for the company and compute the direct material price variance, assuming the material price variance is the responsibility of the company's purchasing agent.
|
|
What is the weighted average-cost of capital of the company
: Your company has 14 million shares of common stock outstanding. The common stock currently sells for $34 per share and has a beta of 1.2. The market risk premium is 10.5 percent and T-bills are yielding 2.0 percent.
|
|
Explain water over the temperature range
: Assume that the coffee has the same density, 0.997g/mL , and specific heat capacity, 4.184\rm J/(g\cdot K) , as water over this temperature range.
|
|
What transfer price would you recommend
: What transfer price would you recommend and why and what transfer price would you recommend if the Battery Division is now selling 1,000,000 batteries a year to retail outlets?
|
|
Does the resulting relation have an mvd
: 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.
|
|
How far will it travel before coming to a stop
: Assuming a coefficient of friction of 0.45 between the disk and the surface it slides on, how far will it travel (after leaving the spring) before coming to a stop
|
|
Calculate afcs total assets turnover and return on assets
: Last year, AFC's sales (all on credit) were $468,000, and it had a net profit margin of 8 percent. The cost of goods is 60 percent of sales. Inventory was tured over 12 times during the year, and the DSO was 42 days.
|
|
Allocate the service department costs
: Allocate the service department costs to the user departments using the step method.
|
|
Evaluate the percent by mass of potassium nitrate
: Calculate the percent by mass (m/m) of potassium nitrate in a solution made from 45.0 g KNO3 and 295 mL water
|