Given the following E-R diagram, map it to a relational schema. First draw a relational schema...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Given the following E-R diagram, map it to a relational schema. First draw a relational schema (box diagram with foreign keys shown). Then write the SQL Data Definition Language statements necessary to create the relational schema. NOTE: {{ }} denotes multi-valued attribute driverid Driver driverName {{drvierPhone)) Drives BusRoute routeld routeName totalLength Date Driven On busid bus numberOfSeats Bus Stop Composed Of stopNum ++ city fare Drives BusRoute routeld routeName totalLength a) Relational Schema: Date Driven On BusStop Composed Of stopNum H city fare b) Data Definition Language (DDL) from the schema: StudentAccount studentID balance • studentID is the primary key of the StudentAccount table. Write an SQL stored procedure called update Balance that takes as input: . a student ID, as an integer • a transaction code ('W' for withdrawal or 'D' for deposit) ● amount to be put against the account. The procedure should update the balance for the specified student and account based on the transaction type- deposits increase and withdrawals decrease an account balance. If there is an invalid transaction code the procedure should generate an exception with a custom error code of 45001 and a message of "Invalid Transaction Code". Given the following E-R diagram, map it to a relational schema. First draw a relational schema (box diagram with foreign keys shown). Then write the SQL Data Definition Language statements necessary to create the relational schema. NOTE: {{ }} denotes multi-valued attribute driverid Driver driverName {{drvierPhone)) Drives BusRoute routeld routeName totalLength Date Driven On busid bus numberOfSeats Bus Stop Composed Of stopNum ++ city fare Drives BusRoute routeld routeName totalLength a) Relational Schema: Date Driven On BusStop Composed Of stopNum H city fare b) Data Definition Language (DDL) from the schema: StudentAccount studentID balance • studentID is the primary key of the StudentAccount table. Write an SQL stored procedure called update Balance that takes as input: . a student ID, as an integer • a transaction code ('W' for withdrawal or 'D' for deposit) ● amount to be put against the account. The procedure should update the balance for the specified student and account based on the transaction type- deposits increase and withdrawals decrease an account balance. If there is an invalid transaction code the procedure should generate an exception with a custom error code of 45001 and a message of "Invalid Transaction Code".
Expert Answer:
Answer rating: 100% (QA)
ANSWER a Relational Schema To create a relational schema the following steps should be followed Step ... View the full answer
Related Book For
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel
Posted Date:
Students also viewed these databases questions
-
Can the conceptual framework of bankruptcy be reconceptualized through interdisciplinary lenses, incorporating insights from behavioral economics, game theory, and institutional economics, to...
-
BMO has recently sent you a promotion that allows you to transfer your $13,700 balance from your existing credit card, which currently charges you an annual rate (APR) of 19.1%, to a new credit card...
-
A Tweets entity in a relational database for social media has the following attributes: TweetID (the primary key, an integer number, required), UserName (text, maximum 15 characters, required), and...
-
Kiev Corp. was incorporated on January 2, 2020, but was unable to begin manufacturing activities until July 1, 2020, because new factory facilities were not completed until that date. The Land and...
-
The following transactions occurred during July 2015 from Dragon Boat, Inc.'s operations: Requirements 1. For each T-account, calculate the missing amount. 2. Explain transactions (1) to (4), which...
-
The following Excel ogive shows toy sales by a company over a 12-month period. What conclusions can you reach about toy sales at thiscompany? 120 E 80 an. Feb. Mar. Apr. May June July Aug. Sept. Oct....
-
What is a system?
-
Falcon Design is a website design and consulting firm. The firm uses a job cost system in which each client is a different job. Falcon Design traces direct labor, licensing costs, and travel costs...
-
Write a PHP program in a single webpage considering the following steps. (a) A block of codes to make a connection to a MySQL database named db_MY using PDO. (2 marks) (b) A block of codes in a form...
-
The following data relates to Prism traders limited for the year ended 31st December, 2018. Shs Cash and marketable securities Fixed Assets Sales Net Income Quick Ratio Current Ratio Average...
-
1. Start the pump following the standard procedure detailed in the manual. 2. Adjust the pump flow using the brench regulating valve. 3. Use the variable speed control unit to correct any changes in...
-
During a given year, the following activities occur: i. A silver mining company pays its workers $200,000 to mine 75 pounds of silver. The silver is then sold to a jewelry manufacturer for $300,000....
-
Suppose that you are the owner of a professional baseball team in a major city, and MLB allows a second team to locate in your city. On what basis should you be compensated?
-
How might the policy changes in parts a through e affect the wage gap between low-skill and high-skill workers in the United States? a. Increased spending on computers in public schools. b....
-
Suppose that most of the teams in a given league are owned by individuals, while two are owned by corporations. If the profits of the individually owned teams rise, but that of the corporate-owned...
-
For each of the economic changes listed in parts \(a\) and \(b\), assess the likely impact on the growth rate and the level of output over the next five years and over the next five decades. a. A...
-
Missisauga is planning to bid for an 8-year government contract. The contract will pay Tk 30,000 next year. The payment will increase by Tk 2,000 every year until the final year. The equipment...
-
The electric field due to a line charge is given by where l is a constant. Show that E is solenoidal. Show that it is also conservative. E =
-
Of what value is the use of parentheses when making use of the rules of operator precedence?
-
Why is it important to be aware of the distinction between the Char and Varchar data types?
-
What is a relation schema? What is the difference between a relation, a relation schema, and a relational schema?
-
Of the following, which is the most comprehensive definition of a project? a. An environment created to deliver a product, service, or result. b. A collaborative enterprise that delivers a product,...
-
How do project management and quality management complement each other? a. Both focus on continuous improvement, management responsibility, customer satisfaction, and prevention over inspection. b....
-
When creating a project management plan, which of the following is the best choice to create it? a. Project manager b. Project manager and team c. Team d. Sponsor
Study smarter with the SolutionInn App