logo

Entity Relationship Modelling Assignment

17 Pages2659 Words240 Views
   

Added on  2021-02-20

Entity Relationship Modelling Assignment

   Added on 2021-02-20

ShareRelated Documents
Database ConceptsProject 11
Entity Relationship Modelling Assignment_1
ContentsINTRODUCTION...........................................................................................................................31. SQL..............................................................................................................................................32.Relational Modelling....................................................................................................................63. Normalisation..............................................................................................................................84. Entity Relationship Modelling...................................................................................................105. Relational Schema Mapping......................................................................................................13CONCLUSION..............................................................................................................................15REFERENCES..............................................................................................................................162
Entity Relationship Modelling Assignment_2
INTRODUCTIONA database can be organized in the collection of data or information, usually accessed andstored the information electronically from computer system. It is more complex that they oftendeveloped by using the modelling and design methods. In this report, it will use SQLite tool thatinteract with the applications and end users to easily capture or analyse data effectively. Itencompasses the basic facilities which provided to the database administrator, who are havingpower to maintain entire data in proper manner. 1. SQL SQL stands for structure query language that support to store, manipulate and retrieving datain the database system (Elmasri and Navathe, 2017). It is considered the standard language incontext of relational database management and also implement the specific actions such asupdate, insert, modify, delete data. 1.1 By using in subquery to find title, give name and family name of all academic who work inthe departments located in Queensland. SELECT Academic.deptnum, Academic.famname, Academic.givename,Academic.title, department.stateFROM Academic INNERJOINdepartment ON Academic.deptnum = department.deptnumWHERE (department.stateLIKE'queensland');1.2 Show the list of all title of field of interest to doctors that work in Queensland. Find output offield name without any duplications.SELECT Field.fieldnum, Field.title, interest.descrip, department.deptname,department.stateFROM department INNERJOINField ON department.fieldnum = Field.fieldnum INNERJOINinterest ON Field.fieldnum = interest.fieldnumWHERE (department.stateLIKE'Queensland');1.3 Display the list of academic, who are collaborating with the another academic on more thanon paper. SELECT Academic.acnum,count(*)AS PaperFROM Paper INNERJOINAcademic ON Paper.acnum = Academic.acnumgroupby Academic.acnumorderby Academic.acnum;3
Entity Relationship Modelling Assignment_3
1.4List the number of each academic to meet specific conditions.select Academic.acnum from Academic leftjoin Author on Author.acnum = Academic.acnumleftjoin Papaer on Papaer.panum = Author.panum and Papaer.title like'%database%'leftjoin interest on interest.acnum = Academic.acnumleftjoin field f on Field.fieldnum = interest.fieldnum and field.title like'%database%'groupby Academic.acnumhavingcount (distinct Papaer.panum)> 12orsum (field.title isnotnull)> 0;1.5List the academic number of any academic that do not contain title or initials.SELECT acnumfrom Academicwhere title='nnot'and initials='nnot';1.6Find the academics who are interested in the title of natural language processing. SELECT Academic.acnum, Field.titleFROM Academic INNERJOINField ON Academic.acnum = Field.acnumWHERE (Field.title ='NLP');1.7List the academic who have not interested other than natural language processing. SELECT Academic.acnum, field.titlefrom Academic, FieldWHERENOTEXISTS(SELECTDISTINCT Academic.acnumFROM Academic innerJOIN Field ON (Academic.acnum = Field.title))4
Entity Relationship Modelling Assignment_4

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Data Modelling & SQL Language
|18
|2576
|416

(solved) SQL Queries Assignment
|5
|372
|57

Database Design And Development - Assignment
|9
|884
|27

Database Concepts - Study Material and Solved Assignments
|9
|730
|289

Report on Relational Database Principles and Designing Of SQL
|24
|2063
|47

SQL statements for data manipulation and database testing on Employees database
|19
|1637
|351