ISYS1055/1057 Individual assignment Database Concepts
ISYS1055/1057 Individual assignment Database Concepts
Assessment No: 1
Student’s Score cards
Database systems are a key technology for the storage, management, manipulation, and retrieval of structured data. They have an impact on the use of information technology in applications ranging from banking, to travel bookings, to online shopping. In this assignment you will apply the skills and concepts that you have learned about database systems in the course so far.
Question 1. The Relational Model (8 points)
A University database contains four relations: Student, Class, Staff and Enrol. A sample database instance is shown in Figure 1, where primary keys, and parent-child relations for attributes are annotated. The meaning of most attributes is self-explanatory. Some additional notes for attribute meanings are as follows:
- sno: student number
- cno: course number
- eno: employee number
Answer the questions below based on the database instance given in Figure 1.
Question 2. SQL (6 points, 0.6 for each item).
In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial on Canvas (the Oracle section) and other resources for Oracle syntax and useful functions.
The relational schema for the Academics database is as follows:
Some notes on the Academics database:
Download and run the SQL script academics.sql on Canvas (the Oracle section) to define and populate the Academics database in your Oracle account.
Write ONE SQL query for each of questions 2.3--2.10. Each component of an SQL statement must be on a separate line. Your query should not produce duplicates in output, but use DISTINCT only if necessary. Include any explanation as comments starting with “--” so that your SQL queries could be directly executed (in SQL Developer and other SQL client programs).
Question 3. ER (6 points).
You are asked to design the ER diagram for a database to manage the data of an online multiplayer team game called "Legendary League". The requirements are:
Players must register an account to play the game, and have a first name, last name, email address, and a chosen unique username.
The game has a set of pre-defined characters available that a player can choose to play. Each character has a unique character name, can hold one or more items, has one or more abilities, and comes from a particular region. Each item has a unique name and type (where the type is one of "weapon", "shield" or "other").
Each ability also has a unique name, and an associated rating. (For example, the ability "Fireblast" may currently be at rating 2 for a particular character.)
Each player can use one or more characters, and go up in levels at different rates with each character. (For example, player "Joe" may currently be at level 1 with the character "Cindra", and at level 15 with character "Gargan"). Each character comes from a single region, identified by the region's name. (For example, the character "Cindra" may be from the region "Zaund")
According to the above description, construct an Entity Relationship (ER) diagram for the database. Clearly state any assumptions that you need to make. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts that cannot be expressed in the ER diagram in the associated description.
- 1.1. (2 points) Give the schema for each relation. Annotate the primary keys (underline) and any foreign keys (*).
- 1.2. (4 points) Give the CREATE TABLE statements for each relation, including primary key and any foreign key constraints.
- 1.3. (2 points) Give the INSERT INTO statements to create the sample database instance shown in Figure 1.
- DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)
- ACADEMIC(acnum, deptnum*, famname, givename, initials, title)
- PAPER(panum, title)
- AUTHOR(panum*, acnum*)
- FIELD(fieldnum, id, title)
- INTEREST(fieldnum*, acnum*, descrip)
- An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department.
- Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR).
- A research field (FIELD) often attracts many academics and an academic can have interest (INTEREST) in several research fields.
- 2.1. Explain the following query in English. Literal explanations will receive zero marks. SELECT fieldnum, title FROM field where (fieldnum>=500 and fieldnum<=599) or (upper(title) like 'DATA %' or upper(title) like '% DATA %' or upper(title) like '% DATA');
- 2.2. The query below is meant to list the panum, title and author acnum of papers and the research interest (fieldnum) of each author, but it has errors. Give the correct SQL query. Select panum, title From author. Interest, paper Where author.acnum=interest.acnum;
- 2.3. How many academics are there in the department where deptnum=100? Return the total number.
- 2.4. List the titles of all papers in the database, in alphabetical order.
- 2.5. Return the details of research fields which have a title starting with the word "Data". Note that the result should include the fields "Data" or "Data Structures" but not "Databases".
- 2.6. List the panum, title and author acnum of each paper.
- 2.7. Return the famname and givename of academics working for 'RMIT CS' (descrip) with acnum in the range [200..299]. The output should be in alphabetical order of famname and then givename.
- 2.8. List the famname, givename of academics who work for institutions in Victoria. Note that the values for "Victoria" include "VIC" or "Vic".
- 2.9. Are there academics who do not have any title? Print their givename, famname. The list should be in alphabetical order of famname and then givename.
- 2.10. How many institutions are there in the database?
- Your ER diagram must only use the UML class diagram notations from the lecture notes.
- You are encouraged to use LucidChart to complete your ER diagram. You can create a free student account at: http://www.lucidchart.com/
- Some common errors in ER diagrams:
- Every entity must have a primary key.
- The names of all relationships and entities must be unique.
- There are no foreign keys in ER diagrams. They come about as part of mapping an ER model into a relational schema.
Why invest in our services?
Our assignment help team is trained to provide you high quality writing services.
High scores achieved by our students is a portrayal of our high quality online assignment help
You can place your assignment order through 4 easy modes of communication