Assignment 2 - Febuary 12th, 2025

Information Management

What are the differences between relation schema, relation, and instance? Give an example using the university database to illustrate.

Draw a schema diagram for the following bank database: - branch(branch_name, branch_city, assets) - customer(ID_customer_name, customer_street, customer_city) - loan(loan_number, branch_name, amount) - borrower(ID, loan_number) - account(account_number, branch_name, balance) - depositor(ID, account_number)

Consider the above bank database. Assume that branch names (branch_name) and customer names (customer_name) uniquely identify branches and customers, but loans and accounts can be associated with more than one customer.

i. What are the appropriate primary keys? (Underline each in diagram)

In the given bank database, the primary keys are chosen based on the attributes that uniquely identify each record in its respective relations. Since the problem states that branch names uniquely identify branches, the attribute branch_name serves as the primary key for the branch relation. Similarly, because customer names uniquely identify customers, the attribute customer_name is the primary key for the customer relation. In the loan relation, each loan is uniquely identified by loan_number, so it becomes the primary key. For the account relation, account_number uniquely identifies each account and therefore is its primary key. However, the problem also states that loans and accounts can be associated with more than one customer, which creates many-to-many relationships. To handle this, the borrower relation uses a composite primary key consisting of (customer_name, loan_number) to uniquely identify each customer–loan combination. Likewise, the depositor relation uses a composite primary key (customer_name, account_number) to uniquely identify each customer–account association. These composite keys ensure that multiple customers can share loans or accounts while still maintaining unique records in the relationship tables.

ii. Given your choice of primary keys, identify appropriate foreign keys.

In this bank database, branch_name and customer_name serve as primary keys for the branch and customer relations, respectively, since they uniquely identify each entity. Similarly, loan_number and account_number are primary keys for the loan and account relations. Because loans and accounts can be associated with multiple customers, the borrower and depositor relations use composite primary keys: (customer_name, loan_number) and (customer_name, account_number). Foreign keys link the tables together: loan.branch_name and account.branch_name reference branch(branch_name), while borrower.customer_name and depositor.customer_name reference customer(customer_name). Similarly, borrower.loan_number and depositor.account_number connect to loan and account. Overall, they keep the data consistent and capture the many-to-many relationships between customers, loans, and accounts.

Describe two ways artificial intelligence or LLM can assist in managing or querying a database. In your answer, briefly explain how each method improves efficiency or accuracy compared to traditional (non-AI) approaches. (3-5 sentences)

Language Learning Models (LLMs) can offload the work that goes into databases by letting you ask questions in plain English and having the system turn them into SQL for you. This can simplify queries and unburden a team or department. LLMS can also aid in the analysis of the data and help identify patterns, trends, or unusual activity - which can help a team uncover problems within a set that would of taken them longer to find before. Overall, it’s faster and more reliable than traditional methods since it can handle large amounts of data automatically without someone having to check everything manually.