The following relational diagram is given for the database of a simple online messaging application like...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
The following relational diagram is given for the database of a simple online messaging application like WhatsApp. Phone (PNo, OpName) User (UID, UName, UDesc) Message (MID, Text, DateTime, Status) Group (GID, Gname, GDesc, AdminID, CTime) Foreign Key (AdminID) → User (UID) Link (PNo, UID, Activation) Foreign Key (PNo) → Phone (PNo) Foreign Key (UID) → User (UID) DSend (MID, SUID, RUID, Status) Foreign Key(MID) → Message(MID) Foreign Key(SUID) → User (UID) Foreign Key(RUID) → User (UID) In (UID, GID) Foreign Key(UID) → User (UID) Foreign Key(GID) → Group (GID) GSend (MID, UID, GID, Status) Foreign Key(MID) →→ Message (MID) Foreign Key(UID) →→ User (UID) Foreign Key(GID) → Group (GID) Phone: Phone can be thought as phone number. It contains a unique number and the operator's name. User: The user is the one who uses the application. A user has a unique ID, a name and a description. Message: Messages have a unique ID, a text for the context and a datetime attribute called Date Time. Status can be "Sent", "Delivered" or "Read". Example datetime format: "YYYY-MM-DD HH:MM:SS" Group: Groups have a unique ID, a group name, a description and the admin's user ID. In this application, there can be only one admin. CTime stands for creation time. It has the same format as datetime. Link: It associates phone numbers with users. A user can have multiple phone numbers, but a phone number can be linked to at most one user. The Activation attribute shows which phone number is used to activate the app. For one user, only one number can be active. Activation is a boolean value which can be "True" or "False". DSend: Short for direct send. Two users are associated with this table: sender and receiver. SUID is the sender's user ID, and RUID is the receiver's user ID. A sender can send multiple messages to multiple receivers and vice versa. In: It shows which user is in which group. A user can be in multiple groups; inside one group, there can be multiple users. GSend: Short for group send. A user and a group are associated with this table. A user can send messages to multiple groups; in one group, multiple people can send messages. 1. Draw the E/R Diagram that corresponds to the given relational schema. Write any assumptions that you make. You may use draw.io or draw it on paper. (10 points) For the rest of the questions, write the corresponding Relational Algebra expression to the given queries. (10 points each) 2. Find the IDs and descriptions of the groups named "Bilkent 2024". (10 points) 3. Find the names of the users who sent a message to a group before the 13th of September 2023. (10 points) 4. Find the names of the users who sent a message saying "Hi!" both to a group and a user. (10 points) 5. For the users who participated in all groups, find the name and description of the groups where they are also the admin of the groups. (10 points) 6. Find the name and the ID of the user who has never sent a group message but sent at least one direct message. (10 points) 7. Find the texts of the messages directly sent to a user that have never been read yet, along with the sender's and receiver's names. (10 points) 8. Find names, active phone numbers, and operator names of users with at least two phone numbers. (10 points) 9. Find the names of the users who were once in a group and sent a message that was read by everyone, and then exited from the group. (10 points) 10. Find the name and the active phone number of the person who sent the last message to the oldest group. (10 points) The following relational diagram is given for the database of a simple online messaging application like WhatsApp. Phone (PNo, OpName) User (UID, UName, UDesc) Message (MID, Text, DateTime, Status) Group (GID, Gname, GDesc, AdminID, CTime) Foreign Key (AdminID) → User (UID) Link (PNo, UID, Activation) Foreign Key (PNo) → Phone (PNo) Foreign Key (UID) → User (UID) DSend (MID, SUID, RUID, Status) Foreign Key(MID) → Message(MID) Foreign Key(SUID) → User (UID) Foreign Key(RUID) → User (UID) In (UID, GID) Foreign Key(UID) → User (UID) Foreign Key(GID) → Group (GID) GSend (MID, UID, GID, Status) Foreign Key(MID) →→ Message (MID) Foreign Key(UID) →→ User (UID) Foreign Key(GID) → Group (GID) Phone: Phone can be thought as phone number. It contains a unique number and the operator's name. User: The user is the one who uses the application. A user has a unique ID, a name and a description. Message: Messages have a unique ID, a text for the context and a datetime attribute called Date Time. Status can be "Sent", "Delivered" or "Read". Example datetime format: "YYYY-MM-DD HH:MM:SS" Group: Groups have a unique ID, a group name, a description and the admin's user ID. In this application, there can be only one admin. CTime stands for creation time. It has the same format as datetime. Link: It associates phone numbers with users. A user can have multiple phone numbers, but a phone number can be linked to at most one user. The Activation attribute shows which phone number is used to activate the app. For one user, only one number can be active. Activation is a boolean value which can be "True" or "False". DSend: Short for direct send. Two users are associated with this table: sender and receiver. SUID is the sender's user ID, and RUID is the receiver's user ID. A sender can send multiple messages to multiple receivers and vice versa. In: It shows which user is in which group. A user can be in multiple groups; inside one group, there can be multiple users. GSend: Short for group send. A user and a group are associated with this table. A user can send messages to multiple groups; in one group, multiple people can send messages. 1. Draw the E/R Diagram that corresponds to the given relational schema. Write any assumptions that you make. You may use draw.io or draw it on paper. (10 points) For the rest of the questions, write the corresponding Relational Algebra expression to the given queries. (10 points each) 2. Find the IDs and descriptions of the groups named "Bilkent 2024". (10 points) 3. Find the names of the users who sent a message to a group before the 13th of September 2023. (10 points) 4. Find the names of the users who sent a message saying "Hi!" both to a group and a user. (10 points) 5. For the users who participated in all groups, find the name and description of the groups where they are also the admin of the groups. (10 points) 6. Find the name and the ID of the user who has never sent a group message but sent at least one direct message. (10 points) 7. Find the texts of the messages directly sent to a user that have never been read yet, along with the sender's and receiver's names. (10 points) 8. Find names, active phone numbers, and operator names of users with at least two phone numbers. (10 points) 9. Find the names of the users who were once in a group and sent a message that was read by everyone, and then exited from the group. (10 points) 10. Find the name and the active phone number of the person who sent the last message to the oldest group. (10 points)
Expert Answer:
Related Book For
Accounting Information Systems
ISBN: 978-0133428537
13th edition
Authors: Marshall B. Romney, Paul J. Steinbart
Posted Date:
Students also viewed these programming questions
-
According to Alcntara Armando, it is a very important element to understand the direction that university systems are following, particularly in developing countries. Select one: The economic and...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
At the end of its accounting period, December 31, of Year 1, Hsu's Financial Services has assets of $575,000 and stockholders' equity of $335,000. Using the accounting equation and considering each...
-
Explain the benefits of graphs over tables in presenting data.
-
1. A doctor studies the known cancer patients in a certain town. The probability that a randomly chosen resident has cancer is found to be .001. It is found that 30% of the town works for Ajax...
-
The actual demand for the patients at Omaha Emergency Medical Clinic for the first six weeks of this year follows: Week Actual No. of Patients 1 ............ 65 2 ............ 62 3 ............ 70 4...
-
Define what is meant by an audit population. What is the importance of an auditor's characteristic of interest when defining an audit population?
-
On June 1, Bash Salad Dressings creates a petty cash fund with an imprest balance of $450. During June, Al Franklin, the fund custodian, signs the following petty cash tickets: On June 30, prior to...
-
The call industry has become increasingly competitive in recent years, with the entry of many overseas call centers. However, after years of outsourcing, call centers have returned to the US. After...
-
Company A is a global company based in the United States that operates in the financial industry. Company A serves its customers with financial products, such as checking accounts, bank cards, and...
-
Here is some data from a random sample. X: -3, 6, 7, 4, 1 Find: 10. mean, median = Group of answer choices mean = 3, median = 4 mean = 4, median =5.5 mean = 15, median = 4 mean = 3, median = 7 11....
-
Lincoln Electric Company in Cleveland, Ohio, manufactures arc welding equipment. Lincoln has the highest paid workers in this extremely competitive market, and it is protected neither by patents nor...
-
Bank assets equal bank liabilities plus bank capital. a. Bank assets are the uses for bank funds. i. They include reserves, securities, and loans. ii. Over the years, commercial and industrial loans...
-
Vanessa Jones is the manager of the phenolics department of PlastiTech, a manufacturer of industrial plastics and composite materials. She is facing a dilemma. There is an opening in her department,...
-
Consider a bank with the following balance sheet, as shown on the next page. You read online that the banks return on assets (ROA) was 1 percent. What were the banks after-tax profits? Bank Balance...
-
Banks face several types of risk in day-to-day business. They include: a. Liquidity riskthe risk that customers will demand cash immediately. i. Liability-side liquidity risk arises from deposit...
-
Find the differential coefficient of y= 2/ (2t-5)^4
-
What is the difference between the straight-line method of depreciation and the written down value method? Which method is more appropriate for reporting earnings?
-
A purchasing department received the following e-mail. Dear Accounts Payable Clerk, You can purchase everything you need onlineincluding peace of mindwhen you shop using Random Account Numbers (RAN)....
-
As in all areas of IT, DBMSs are constantly changing and improving. Research how businesses are using DBMSs, and write a report of your findings. Address the following issues: 1. Which popular DBMS...
-
Although XBRL facilitates the electronic exchange of financial information, some external users do not think it goes far enough. They would like access to the entire general ledger, not just to...
-
Determine the probability mass function of \(X\) from the following cumulative distribution function: \[ F(x)=\left\{\begin{array}{lr} 0 & x. \] Figure 3.3 displays a plot of \(F(x)\). From the plot,...
-
The probability that a wafer contains a large particle of contamination is 0.01. If it is assumed that the wafers are independent, what is the probability that exactly 125 wafers need to be analyzed...
-
Two new product designs are to be compared on the basis of revenue potential. Marketing believes that the revenue from design A can be predicted quite accurately to be \(\$ 3\) million. The revenue...
Study smarter with the SolutionInn App