Assignment 2: Relational Schema and Database Concepts

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

• According to the SKS textbook in chapter 2, a relation schema consists of a list of attributes and their corresponding domains. An example of a relation schema using the university database is the instructor table, specifically the columns where a list of attributes says INSTRUCTOR(id, name, dept_name, salary)

  

• Second, the textbook states that the concept of a relation corresponds to the programming language notion of of type definition. An example of a relation using the university database would be instructor table itself:

  

• Third, the textbook states that the concept of a relation instance corresponds to the programming-language notion of a value of a variable. The value of a given variable may change with time; similarly the contents of a relation instance may change with time as the as the relation is updated. An example of a relation instance using the university database is the bottom part of the instructor relation (table), where the variables are in rows that could be updated and changed over time.

  

2. Draw a schema for the following bank database:

3. Consider the above 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)

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

4. Describe two ways artificial intelligence of LLM can assist in managing or querying a database.

• According to Google AI, artificial intelligence and large language models can translate human questions into precise database commands through the enabling of natural language querying (Text-to-SQL). Additionally, automated database administration and optimization is also effective, where AI monitors data access patterns to optimize query execution commands, indexing, and data sharding.

• ChatGBT recommends that traditional databases require SQL knowledge, whereas NQL simplifies SQL queries for the user. For automated database administration, traditional DBA tasks are done manually (indexing, query tuning, resource allocation), whereas ADA helps adjust these skills.

• Overall, AI and LLM helps humans with assisting SQL queries and database management, which gives an increasing advantage for database managers and administrators.