ISYS1057 Database Concepts Assignment - RMIT University, Australia

ORDER NEW ISYS1057 DATABASE CONCEPTS ASSIGNMENT AT NOMINAL PRICE!

Question 1 - SQL

1.1. For each academic, give the acnum, givename, famname and the total number of papers s/he has written. Note that if an academic has not written any paper, his/her total should be zero. You can use or not use JOIN operators.

SELECT paper.acnum,count(paper.panum) FROM author group by paper.acnum UNION SELECT 0 AS paper.panum ,'no record ' AS paper.description , 0 as paper.panum;

1.2. List departments where at least one academic does not have any research interest. List the deptnum, deptname and instname of these departments. You must use a subquery.

select deptnum,deptname,instname

from department where academic.acnum in(

select author.acnum

from author

union

(select paper.panum

from academic

left outer join department on ( academic.deptnum = department.deptnum )

left outer join author on ( academic.acnum = author.acnum ) left outer join paper on ( author.panum = paper.panum ))

1.3. List the fieldnum, title and the total number of interested academics (under the heading "NO. ACADEMICS INTERESTED") for each research field that some academics are interested in. The list should be in increasing order of fieldnum. Note: research fields that no academics are interested in are excluded.

Select field.fieldnum from fleld inner join select count(interest.acnum) from interest where field.fileldnum=interest.fieldnum";

1.4. Find research fields that have at least ten interested academics. Give the fieldnum, title and the number of interested academics for these research fields.

Select field.fieldnum,field.title from field where(select field.title, cademic.acnum from field,academic where count(academic.acnum)=10 group by field.title

1.5. Return the acnum of academic(s) who wrote the largest number of papers. You must NOT use MAX. An SQL query that lists all academics in decreasing order of their total number of papers is incorrect.

select a.acnum, a.givename, a.famname, interest.fieldnum

from academic a, interest

where a.acnum = interest.acnum

having count (interest.acnum) > 1;

GET BENEFITTED WITH QUALITY ISYS1057 DATABASE CONCEPTS ASSIGNMENT HELP SERVICE OF EXPERTSMINDS.COM!

1.6. Give the total number of academics who have not written any papers. You must use the NOT IN operator.

Select count(academic.acnum) from academic , interest where academic.acnum not in interest.acnum;

1.7. Find research fields whose title contains the string 'data' and has at least one interested academic from the department with deptnum 100. List the fieldnum and title of these research fields. You must use the EXISTS operator. Ensure your query is case-insensitive.

Select paper.panum,paper.title from paper, author where exists (select paper.title,author.acnum from paper , author where paper.title="%data%" and acnum=100)

1.8. List papers (panum) that have authors from the same department. Do NOT use any JOIN operator. Do NOT use any subqueries.

Select panum from paper, academic, department where academic.deptnum=department.deptnum

1.9. The SQL query below is meant to list research fields (fieldnum) where no academics from 'RMIT CS' (deptnum=126) have an interest. But it is incorrect. Give the correct SQL query.

select fieldnum

from interest, academic

where interest.acnum=academic.acnum and deptnum != 126;.

1.10. Consider the SQL query given below. Give the English explanation for the output of a) the subquery, and b) the whole SQL query. Literal explanation will receive zero marks.

select S.acnum

from interest S

where upper(S.descrip) like '%LOGIC%'

and exists (select fieldnum

from interest

where acnum = S.acnum

and upper(descrip) not like '%LOGIC%');

a. The sub query displays the field num if descript value change to upper case and check not like LOGIC and academic num from academic and academic num from interest.

b. The whole sql query display that exists of the subquery return true then subquery have one or more rows.

ORDER NEW COPY OF ISYS1057 DATABASE CONCEPTS ASSIGNMENT & GET HIGH QUALITY SOLUTIONS FROM SUBJECT'S TUTORS!

Question 2 - The Relational model

Consider the Customer Order database of the ABC company, which keeps data for customers and their orders. A database of only one relation is designed, as shown below:

ABC(customerNo, customerName, phone, streetAddr, city, state, postCode, orderNo, productNo, quantity, unitPrice, salesRep)

2.1. Give all likely FDs.

Functional Dependency is attribute of a table uniquely identifies another attribute of the same table. In our example functional dependency is in

Customer table: customerName is functionally dependent on customerNo

phone is functionally dependent on customerNo

Product table: Do not have any functional dependency on productNo because quantity, unitprice may be equal to other products and sales rep can also sell other products.

Order table: ProductNo is functionally dependent on orderNo

2.2. Give the candidate keys for the ABC relation.

The minimal set of attributes which can uniquely identify a tuple is called candidate key. In our example ABC database has customerNo, orderNo, productNo, are candidate key.

2.3. Give {CustomerNo}+ and {orderNo, salesRep}+ based on the FDs for Question 2.1).

The set of attributes that are functionally dependent on the attribute A is called Attribute Closure of A and it can be represented as A+.

R(customerName, street,city,postcode)

FD={customerNo->customerName, customerNo->street,customerNo->city,

customerNo->Post).

(customerNo)+={customerNo,customerName, phone, streetAddr, city, state, postCode }

(customerName)+={customerSurname,customeFirstname}

(customerName)+={customerName}

(street)+={stree}

(phone)+={phone}

(city)+={city}

(post)+={post}

R(productNo, quantity, unitPrice, salesRep )

FD={productNo->productName, productNo->quantity, productNo->unitprice, productNo->salesRep}

R=(orderNo)

FD={orderNo}

(OrderNo)+={orderNo}

2.4. Is the relation ABC in BCNF or 3NF?

customerName,customerAddress,phone,phone,city,post,productNo,quanity, unitprice,salesRep,orderno

A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table. In our example all attributes are depends on super key. In doctor table all attributes are depends on customerNo , product table all attributes of depends on productNo and order table orderDatetime also depends on orderNo So ABC relation is in BCNF.

SAVE TOP GRADE USING ISYS1057 DATABASE CONCEPTS ASSIGNMENT HELP SERVICE OF EXPERTSMINDS.COM!

Question 3 - Normalisation

A shop keeps data about customers, salesmen, products and orders. Consider the attributes below and their associated FDs, where empID is the employee ID for salesmen.

3.1. Give the minimal basis for the given FDs.

1. orderID → custID

orderID → amount_due, discount further divide this into

orderID → amount_due

orderID → discount

We can simplify

orderID → custID

cusID → amount_due

1. orderID, itemNo → quantity

orderID → quantity

itemNo->quantity

2. custID, orderID → amount_due, discount

custID, orderID → amount_due

custID, orderID → discount

custID-> amount_due

orderID-> discount

3.2. The Transaction relation is not in BCNF or 3NF. Give the reason using the FDs on attributes.

A table design is said to be in 3NF if both the following conditions hold:

The transaction relation is not in BCNF because this table in 2NF transitive functional dependency of non-prime attribute on any super key should be removed. The transaction table fulfil 3NF.

3.3. Follow the 3NF decomposition algorithm to decompose the Transaction relation into relations in BCNF or 3NF. Give; The relations after decomposition. Specify the primary key for each relation. Specify any foreign keys for each relation.

Transaction into relations in BCNF or 3NF. Give the relations after decomposition and specify the primary key and any foreign keys for each relation.

Transaction(transactionid,orderid*)

Order(orderid,quantity,discount)

Customer(customerid,orderid*,amount_due)

DO YOU WANT TO EXCEL IN ISYS1057 DATABASE CONCEPTS ASSIGNMENT - ORDER AT EXPERTSMINDS!

Question 4 - ER model

Give the ER diagram for the database using the UML class symbols (as used in the lecture notes), making assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your diagram. Explain any constraints that cannot be expressed in the diagram.

ER Model:

Question 5 - ER to relational schema mapping

Map the diagram to a relational database schema. Indicate the primary key (underline) and any foreign keys (asterisk) in each relation.

Answer:

Relational Schema:

empNo->empgivename

empNo->empsurname

RunNo->time

RunNo->direction

RunNo->express

LineNo->name

LineNo->length

stationName->premium

stationName->lineNo

lineNo->runNo

empNo->runno

empNo->stationName

empNo->lineNo

Relation Schema:

Employee(empID,givename,surname,stationName*,lineName*,RunNo*)

Run(number,time,direction,express,linename*,empid*)

Line(name,length,empid*,stationname*)

station(name,premium,empid*)

DON'T MISS YOUR CHANCE TO EXCEL IN ISYS1057 DATABASE CONCEPTS ASSIGNMENT! HIRE TUTOR OF EXPERTSMINDS.COM FOR PERFECTLY WRITTEN ISYS1057 DATABASE CONCEPTS ASSIGNMENT SOLUTIONS!

Acquire our RMIT University, Australia Assignment Help services for major related courses and academic units such as -

• ISYS1039 Business Analysis and Impact Management Assignment Help
• ISYS2038 Database Design and Development Assignment Help
• ISYS1168 Digital Curation Assignment Help
• ISYS2396 Enterprise Systems Assignment Help
• ISYS2421 Business Data Management and Analytics Assignment Help
• ISYS3374 Business Analytics Assignment Help
• ISYS1108 Software Engineering Project Management Assignment Help
• ISYS1117 Software Engineering Fundamentals Assignment Help
• ISYS1084 Object Oriented Software Design Assignment Help