A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions




For our sample database application, consider a UNIVERSITY database that keeps track of students and their majors, transcripts, and registration as well as of the university’s course offerings. The database also keeps track of the sponsored research projects of faculty and graduate students. This schema is shown in Figure 8.9. A discussion of the requirements that led to this schema follows.

For each person, the database maintains information on the person’s Name [Name],Social Security number [Ssn], address [Address], sex [Sex], and birth date [Bdate].Two subclasses of the PERSON entity type are identified: FACULTY and STUDENT.Specific attributes of FACULTY are rank [Rank] (assistant, associate, adjunct,research, visiting, and so on), office [Foffice], office phone [Fphone], and salary[Salary]. All faculty members are related to the academic department(s) with which they are affiliated [BELONGS] (a faculty member can be associated with several departments, so the relationship is M:N). A specific attribute of STUDENT is [Class] (freshman=1, sophomore=2, ..., graduate student=5). Each STUDENT is also related to his or her major and minor departments (if known) [MAJOR] and [MINOR], to the course sections he or she is currently attending [REGISTERED], and to the courses completed [TRANSCRIPT].

Each TRANSCRIPT instance includes the grade the student received [Grade] in a section of a course. GRAD_STUDENT is a subclass of STUDENT, with the defining predicate Class = 5. For each graduate student, we keep a list of previous degrees in a composite, multivalued attribute [Degrees].We also relate the graduate student to a faculty advisor [ADVISOR] and to a thesis committee [COMMITTEE], if one exists.

An academic department has the attributes name [Dname], telephone [Dphone], and office number [Office] and is related to the faculty member who is its chairperson [CHAIRS] and to the college to which it belongs [CD].

Topics You May Be Interested In
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Weak Entity Types
Database Languages Example Of Notation- Uml Class Diagrams
Database Schema Relationship Types Of Degree Higher Than Two
Sql Data Definition Relationship Between Information Security Versus Information Privacy
Initial Conceptual Design Of The Company Database Database Security And The Dba

Each college has attributes college name [Cname], office number [Coffice], and the name of its dean [Dean].

A course has attributes course number [C#], course name [Cname], and course description [Cdesc].

Several sections of each course are offered, with each section having the attributes section number [Sec#] and the year and quarter in which the section was offered ([Year] and [Qtr]). Section numbers uniquely identify each section. The sections being offered during the current quarter are in a subclass CURRENT_SECTION of SECTION, with the defining predicate Qtr = Current_qtr and Year = Current_year. Each section is related to the instructor who taught or is teaching it ([TEACH]), if that instructor is in the database.

The category INSTRUCTOR_RESEARCHER is a subset of the union of FACULTY and GRAD_STUDENT and includes all faculty, as well as graduate students who are supported by teaching or research. Finally, the entity type GRANT keeps track of research grants and contracts awarded to the university. Each grant has attributes grant title [Title], grant number [No], the awarding agency [Agency], and the starting date [St_date].

Topics You May Be Interested In
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Schema Diagrams
Using High-level Conceptual Data Models For Database Design Relational Operations
Data Storage And Querying Initial Conceptual Design Of The Company Database
Data Mining And Information Retrieval Attribute Specification In Select Clause
Keys Aggregate Functions

A grant is related to one principal investigator [PI] and to all researchers it supports [SUPPORT]. Each instance of support has as attributes thestarting date of support [Start], the ending date of the support (if known) [End],and the percentage of time being spent on the project [Time] by the researcher being supported.

 

A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions

 

Topics You May Be Interested In
View Of Data Database Users And Administrators
Introduction To Transaction Processing Basic Structure Of Sql Queries
Database Languages Entity Types, Entity Sets, Keys, And Value Sets-2
Relational Databases Refining The Er Design For The Company Database
Relational Databases Relationship Between Information Security Versus Information Privacy

 

 

 

 

Topics You May Be Interested In
A Sample University Eer Schema, Design Choices, And Formal Definitions Basic Structure Of Sql Queries
Introduction To Transaction Processing A Sample Database Application
Database Design Weak Entity Types
Overview Of Object Database Concepts Ordering The Display Of Tuples
Overview Of The Sql Query Language Sensitive Data And Types Of Disclosures

 

 

 

 

Topics You May Be Interested In
Specialization And Generalization Schema Diagrams
A Sample University Eer Schema, Design Choices, And Formal Definitions Basic Structure Of Sql Queries
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Introduction To Transaction Processing Initial Conceptual Design Of The Company Database
Relational Databases Null Values

 



Frequently Asked Questions

+
Ans: it is sometimes necessary to represent a single superclass/subclass relationship with more than one superclass, where the superclasses represent different entity types. In this case, the subclass will represent a collection of objects that is a subset of the UNION of distinct entity types. view more..
+
Ans: we discuss differences between specialization/generalization lattices (multiple inheritance) and hierarchies (single inheritance), and elaborate on the differences between the specialization and generalization processes during conceptual database schema design. We discuss constraints that apply to a single specialization or a single generalization. view more..
+
Ans: Specialization is the process of defining a set of subclasses of an entity type; this entity type is called the superclass of the specialization. We use the term generalization to refer to the process of defining a generalized entity type from the given entity types. view more..
+
Ans: For our sample database application, consider a UNIVERSITY database that keeps track of students and their majors, transcripts, and registration as well as of the university’s course offerings. The database also keeps track of the sponsored research projects of faculty and graduate students. view more..
+
Ans: The basic notation for specialization/generalization is to connect the subclasses by vertical lines to a horizontal line, which has a triangle connecting the horizontal line through another vertical line to the superclass. A blank triangle indicates a specialization/generalization with the disjoint constraint, and a filled triangle indicates an overlapping constraint. view more..
+
Ans: The similarities and differences between conceptual modeling and knowledge representation, and introduces some of the alternative terminology and a few additional concepts.The goal of KR techniques is to develop concepts for accurately modeling some domain of knowledge by creating an ontology that describes the concepts of the domain and how these concepts are interrelated. view more..
+
Ans: Recovery from transaction failures usually means that the database is restored to the most recent consistent state just before the time of failure. To do this, the system must keep information about the changes that were applied to data items by the various transactions. view more..
+
Ans: Several types of locks are used in concurrency control. To introduce locking concepts gradually, first we discuss binary locks, which are simple, but are also too restrictive for database concurrency control purposes, and so are not used in practice. Then shared/exclusive locks - also known as read/write locks - which provide more general locking capabilities and are used in practical database locking systems. view more..
+
Ans: In this section we discuss the concepts of concurrent execution of transactions and recovery from transaction failures. view more..
+
Ans: Conceptual modeling is a very important phase in designing a successful database application. Generally, the term database application refers to a particular database and the associated programs that implement the database queries and updates. view more..
+
Ans: Data is converted into information, and information is then evaluated and organised so that it can be used purposefully as knowledge. view more..
+
Ans: A database system is a collection of interrelated data and a set of programs that allow users to access and modify these data. A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained. view more..
+
Ans: In this section we discuss the concepts of concurrent execution of transactions and recovery from transaction failures view more..
+
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
+
Ans: A database system provides a data-definition language to specify the database schema and a data-manipulation language to express database queries and updates. view more..
+
Ans: Database design mainly involves the design of the database schema. The design of a complete database application environment that meets the needs of the enterprise being modelled requires attention to a broader set of issues. view more..
+
Ans: A relational database is based on the relational model and uses a collection of tables to represent both data and the relationships among those data. view more..
+
Ans: A relational database is based on the relational model and uses a collection of tables to represent both data and the relationships among those data. view more..




Rating - 4/5
508 views

Advertisements