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 occur in each store? (provide the minimum and the maximum number) 1.2. How many credit cards with the same owner and the same type can exist? (provide the minimum and the maximum number) 2) Write the SQL query representing the following request: 2.1. Determine the number and the expire date of the credit cards with a limit equal to 2.000, that have not generated any transaction in 202
J
SHORTAND NOTATION FOR RELATIONAL SQL TABLES
Notation Example Meaning
Underlined A or A, B The attribute(s) is
(are) a primary key
Superscript name
of relation
AR or AR, BR The attribute(s) is
(are) a foreign key
referencing
relation R
As an example, the schema
R(A, B, C, D, ES)
S(F, G, H)
corresponds to the following SQL tables:
CREATE TABLE R
( A <any SQL type>,
B <any SQL type>,
C <any SQL type>,
D <any SQL type>,
E <any SQL type>,
PRIMARY KEY(A),
FOREIGN KEY (E) REFERENCES S(F)
);
CREATE TABLE S
( F <any SQL type>,
G <any SQL type>,
H <any SQL type>,
PRIMARY KEY(F))
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 (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 occur in each store? (provide the minimum and the maximum number)
1.2. How many credit cards with the same owner and the same type can exist? (provide the minimum
and the maximum number)
2) Write the SQL query representing the following request:
2.1. Determine the number and the expire date of the credit cards with a limit equal to 2.000, that have
not generated any transaction in 2022.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
