Students are required to construct a Star Schema for the clinic's medicine consumption information data warehouse. The clinic is interested in the fact of total quantity consumed from the dimensions of the clinic, medicine, and period with attributes aspresented in the following relation schema:Clinic (CliniclD, ClinicName)Medicine (MedicineCode, Description)Period (DateKey, Fiscal Period)Present the Fact table of the Start Schema, including an appropriate table name. its attributes, and keys using Relation Schema notation.

SQL(structured query language) that is used to convert data in relational table by using sql…

tudents are required to construct a Star Schema for the clinic's medicine consumption information data warehouse. The clinic is interested in the fact of total quantity consumed from the dimensions of the clinic, medicine, and period with attributes as resented in the following relation schema: Clinic (CliniclD, ClinicName) Medicine (MedicineCode, Description) Period (DateKey, FiscalPeriod) resent the Fact table of the Start Schema, including an appropriate table name, its attributes, and keys using Relation Schema notation.

tudents are required to construct a Star Schema for the clinic's medicine consumption information data warehouse. The clinic is interested in the fact of total quantity consumed from the dimensions of the clinic, medicine, and period with attributes as resented in the following relation schema: Clinic (CliniclD, ClinicName) Medicine (MedicineCode, Description) Period (DateKey, FiscalPeriod) resent the Fact table of the Start Schema, including an appropriate table name, its attributes, and keys using Relation Schema notation.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNumber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The column facID of relation Classes that references the table Faculty, The column StuID of relation Enrolls that references table Students, and The column classNunber of relation Enrolls that references table Classes. The following SQL statement is intended to add a record to the table Students. Assume that the entered value for each column is appropriate. In other words, there is no domain constraint violation. INSERT INTO Students(stuID, lastName, firstName, major) VALUES('950555111', 'Anna' 'Smith', 'BSIT'); However, this statement does not work correctly. Briefly explain why and list the correct SQL statement.
Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNhmber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The primary keys are underlined. The referential integrity constraints are as follows: The column facID of relation Classes that references table Faculty, The column of stuID of relation Enrolls that references table Students, and The column classNumber of relation Enrolls that references table Classes. The following SQL statement is intended to define the table Students. However, this statement does not work correctly. CREAT TABLE Students( lastName VARCHAR(20), firstName VARCHAR(20), major VARCHAR(40) gpa DECIMAL(3,2), PRIMARY KEY(stuID)); However, this statement does not work correctly. Briefly explain why and list the corrected SQL statement.
According to the university database that we explain in the class, explain how functional dependencies can be used to indicate the following: • A one-to-one relationship set exists between entity sets student and instructor. A Many-to-one relationship set exists between entity sets student and instructor
our task for this assignment is to complete a database design for the given case studies. Model the system described in the case study and draw a Physical Data Model using MySQL Workbench. Your diagram must conform to the notation used in this subject (object notation = ‘Workbench Simplified’, relationships notation = ‘Crows Foot’, show data types). If you include sub-types, place them inside a Layer with a note explaining their relationship to the super-type.
Consider the relational schemapart(part_id, name, cost)subpart(part_id, subpart_id, count)where the primary-key attributes are underlined. A tuple (p1, p2, 3) in the subpartrelation denotes that the part with part_id p2 is a direct subpart of the partwith part_id p1, and p1 has 3 copies of p2. Note that p2 may itself have furthersubparts. Write a recursive SQL query that outputs the names of all subparts ofthe part with part-id 'P-100'.
Develop an Entity Relationships Diagram (ERD) based on the relational schemas in the 3NF for the starting relational schema in un-normalized form presented below. UNF:Order(OrderID, OrderDate, CustID, CustName, CustPhone, CCNum, CCExpDate, CCBank, BnkContName, BnkContPhone, CustEmail, OrderIP, SiteRefFrom, ShipStreet, ShipCity, ShipSt, ShipZip, OrderLineNum, ItemID, ItemName, ItemDesc, ItemQtyOrdered,ItemListPrice, ItemSalePrice, ItemQtyShip, ShipCharge, Tax, TotalDue)
Consider the relation r(A, B, C), where the index is the attribute A. Give an example of a query that can only be answered by looking at the index rather than the relationship's tuples. (Index-only plans are query plans that rely just on the index rather than the actual relation.)
Consider the following schema for a student relation. Student{Student ID, Last Name, First Name, Course ID, Course Section, Course Name, Faculty_ID, Faculty Last Name, Faculty First Name, Faculty Building&Office #, Semester, Grade}Sample Data:A43469873, Clark, Mary, MSIS5663, 01, Data Warehousing, A98706724, Sarathy, Rathindra, BUS407, SP2022, AIn a given semester (Note that there will be multiple semesters): A student may be enrolled in multiple courses, from multiple faculty, and receives a grade for each A faculty may teach multiple sections of multiple coursesIf you make any other assumptions, clearly state them here.a. Assuming that the table is in 1NF, identify all candidate key(s) and your choice of primary key.b. Identify the functional dependencies in the 1NF table above based on the chosen primary key, including any partial and transitive dependencies. (If necessary, clearly state your assumptions). In answering this, you may use the format in question 1d.c. After…
Consider the relation r(A, B, C) with the attribute A as the index. Give an example of a query that can be answered only by looking at the index, rather than the tuples in the relationship. (Index-only plans are query plans that just utilise the index and do not access the actual relation.)
Draw an E-R data model for the Garden Glory database and Use the IE Crow’s Foot E-R model for your E-R diagrams. Justify the decisions you make regarding minimum and maximum cardinality.
Consider a relation with schema R(A,B,C,D,E,F,G). Choose the key for the relation for the following sets of functional dependencies. BCD→A BC→E A→F F→ G C→ D A→ G ? A) B B) C C) BC D) All combinations of attributes are key
Consider a relational database for an online hotel booking company. The database consisting ofthe following tables (where the primary keys are underlined): Hotel (hId, hName, hAddress, hCity) Guest(gId, gName, gAddress, gCity) Room(hid, roomNo, type, price) Booking(gId, hId, roomNo, fromDate, year, noOfDays)Where hId and gId are identifiers for the hotels and the guests,and the Booking relation indicated that a guest booked a hotel room for a specified number of days(noOfDays) starting from fromDate of a given year. For instance, a tuple < g12345, h5555, 220,Jan05, 2016, 15> in Booking indicates that the guest g12345 booked room 220 of the h5555 hotelfor 8 days starting on Jan 5, 2016. The attribute "gAddress" records the home address of theguests.Write the following queries in relational algebra (1) Return thename of guests who have booked a hotel room in NYC in January 2016 (i.e.,fromDate between 01/01 and 01/31 and year=2016).(2) Return thename of guests who have booked a…

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781305971776

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781305971776

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS