Consider the following relational schema, representing five relations describing shopping transactions and information about credit cards generating them [the used notation is explained above]. SHOPPINGTRANSACTION (TransId, Date, Amount, Currency, ExchangeRate, CardNbrCREDITCARD, StoreIdSTORE) CREDITCARD (CardNbr, CardTypeCARDTYPE, CardOwnerOWNER, ExpDate, Limit) OWNER (OwnerId, Name, Surname, BirthDate, Job, IncomeLevel, Level) CARDTYPE (CardType, Circuit, Issuer, Address, Country, CardName, Color) STORE (StoreId, Name, Address, Country, Zone, Web, Email, Type) 1) Answer the following questions considering the domain described by the previous relational schema: 1.1. How many transactions can be generated by a given credit card? (provide the minimum and the maximum number) 1.2. How many shopping transactions occurred in the same store can be associated with a given identifier? (provide the minimum and the maximum number) 2) Write the SQL query representing the following request: 2.1. Determine the total number of shopping transactions, performed during the last validity day of a credit card, whose amount coincides with the credit card limit.
Transcribed Image Text:APRIL
DATA MANAGEMENT
SHORTAND NOTATION FOR RELATIONAL SQL TABLES
Notation
Example
Meaning
CREATE TABLER
The attribute(s) is
(are) a primary key
The attribute(s) is
(are) a foreign key
referencing
Underlined
A or A, B
A cany SQL type>,
B <any SQL type>,
C <any SQL type>,
D <any SQL type>,
E cany SQL type>,
A" or A", B"
Superscript name
of relation
relation R
PRIMARY KEY(A),
As an example, the schema
FOREIGN KEY (E) REFERENCES S(F)
);
R(A, B, C, D, ES)
S(E G, H)
CREATE TABLES
F <any SQL type>,
G <any SQL type>,
H cany SQL type>,
PRIMARY KEY(F))
corresponds to the following SQL tables:
EXERCISE
Consider the following relational schema, representing five relations describing shopping transactions and
information about credit cards generating them [the used notation is explained above].
SHOPPINGTRANSACTION (Transld, Date, Amount, Currency, ExchangeRate, CardNbrCREDITCARD, StoreldSTORE)
CREDITCARD (CardNbr, CardTypeARDTYPE, CardowneroWNER, ExpDate, Limit)
OWNER (Ownerld, Name, Surname, BirthDate, Job, Incomelevel, Level)
CARDTYPE (CardType, Circuit, Issuer, Address, Country, CardName, Color)
STORE (Storeld, Name, Address, Country, Zone, Web, Email, Type)
1) Answer the following questions considering the domain described by the previous relational schema:
1.1. How many transactions can be generated by a given credit card? (provide the minimum and the
maximum number)
1.2. How many shopping transactions occurred in the same store can be associated with a given
identifier? (provide the minimum and the maximum number)
2) Write the SQL query representing the following request:
2.1. Determine the total number of shopping transactions, performed during the last validity day of a
credit card, whose amount coincides with the credit card limit.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
![APRIL
DATA MANAGEMENT
SHORTAND NOTATION FOR RELATIONAL SQL TABLES
Notation
Example
Meaning
CREATE TABLER
The attribute(s) is
(are) a primary key
The attribute(s) is
(are) a foreign key
referencing
Underlined
A or A, B
A cany SQL type>,
B <any SQL type>,
C <any SQL type>,
D <any SQL type>,
E cany SQL type>,
A" or A", B"
Superscript name
of relation
relation R
PRIMARY KEY(A),
As an example, the schema
FOREIGN KEY (E) REFERENCES S(F)
);
R(A, B, C, D, ES)
S(E G, H)
CREATE TABLES
F <any SQL type>,
G <any SQL type>,
H cany SQL type>,
PRIMARY KEY(F))
corresponds to the following SQL tables:
EXERCISE
Consider the following relational schema, representing five relations describing shopping transactions and
information about credit cards generating them [the used notation is explained above].
SHOPPINGTRANSACTION (Transld, Date, Amount, Currency, ExchangeRate, CardNbrCREDITCARD, StoreldSTORE)
CREDITCARD (CardNbr, CardTypeARDTYPE, CardowneroWNER, ExpDate, Limit)
OWNER (Ownerld, Name, Surname, BirthDate, Job, Incomelevel, Level)
CARDTYPE (CardType, Circuit, Issuer, Address, Country, CardName, Color)
STORE (Storeld, Name, Address, Country, Zone, Web, Email, Type)
1) Answer the following questions considering the domain described by the previous relational schema:
1.1. How many transactions can be generated by a given credit card? (provide the minimum and the
maximum number)
1.2. How many shopping transactions occurred in the same store can be associated with a given
identifier? (provide the minimum and the maximum number)
2) Write the SQL query representing the following request:
2.1. Determine the total number of shopping transactions, performed during the last validity day of a
credit card, whose amount coincides with the credit card limit.](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F5241d7bf-3e5a-4742-aa77-3fe3714eeb42%2F414e05b4-48ea-411e-bc68-cd8f90babf58%2F9e3rxq_processed.png&w=3840&q=75)
