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
ContentsINTRODUCTION...........................................................................................................................31. SQL..............................................................................................................................................32.Relational Modelling....................................................................................................................63. Normalisation..............................................................................................................................84. Entity Relationship Modelling...................................................................................................105. Relational Schema Mapping......................................................................................................13CONCLUSION..............................................................................................................................15REFERENCES..............................................................................................................................162
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
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
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Data Modelling & SQL Languagelg...
|18
|2576
|416
(solved) SQL Queries Assignmentlg...
|5
|372
|57
Database Design And Development - Assignmentlg...
|9
|884
|27
Database Concepts - Study Material and Solved Assignmentslg...
|9
|730
|289
Report on Relational Database Principles and Designing Of SQLlg...
|24
|2063
|47
SQL statements for data manipulation and database testing on Employees databaselg...
|19
|1637
|351