Conceptual And Logical Model Of Sun Valley Database System

Question

Task: The Sun Valley college database stores information about a teaching process: ?

• Employees are divided into three categories: staff, administrators and professors
• The following information about all employees needs to be stored: empID, first and last names, and a hire date.
• A staff member can have Level 1 or Level 2 
• Administrator info:  Admin position
• Professor info: specialty and rank 
• A professor may also be an administrator
• The College has five faculties, and, in turn, each faculty is divided into departments 
• Only a professor can be a Dean of a faculty or a Head of a department 
• The faculty information to be stored: facultyID and facultyName
• The Department information to be stored: depatmentID and departmentName 
• A department provides courses (courseID, Title, Description and credits) 
• If a course currently is being taught, then a class with maximum 50 students  is assigned for the course (classID, classTime) with a professor who is teaching the course 
• The following information is stored about students: studentID, first and last names, email 
• Students belong only to one department 
• Students are enrolled in one or more classes 
• A staff member is assigned to each student as an advisor
• A room (roomID, type) is assigned to each class 
•  A college campus consists of several buildings, and each room is associated with a building 

Your assignment consists of two parts.
Part 1. Conceptual Model

• Identify Entities
• Identify entity supertypes and subtypes and their types, inheritance, and discriminators.  
• Describe relationships between entities and their cardinalities 
• Identify entity primary, candidate, and surrogate keys 
• Draw a Conceptual Model Entity-Relationship diagram 

Part 2. Logical Model

• Describe data attributes and their types for each entity
• Identify foreign keys for each entity
• if there are any one-to-one relationships then describe them
• Redesign the Conceptual model removing many-to-many relationships 
• Draw a Logical Model diagram 

Answer

Introduction
This paper aims at delivering the necessary information and details required for developing The Sun Valley database system. This Sun Valley database system paper is divided in two parts including conceptual and logical models design processes and the diagrams representing required details for the database. the college consist of different members and group members who combined to make an environment and the database must be recording all the details for efficient tracking. The provided details have been used for gathering necessary information and building the design.

Part 1. Conceptual Model

How are Entities of Sun Valley database system related
The entities of the database can be listed as employees including professors, administrators, and staffs. Faculties, dean, department, student, course, campus, room and building are other entities those needs to be embedded in the database for managing the college. The reasonable database demonstrating alludes to the comprehension of direction of information from client perspective. The theoretical information model discussed on the Sun Valley database system gives the meaning of the framework it contains. The information planners and business partners are the intended interest group of this model. The motivation behind making the intelligent model is characterizing the business principles and ideas. There are three ideas in applied database displaying, for example, model, pattern and occasion. The model is alluded to as the accumulation of ideas for depicting the database structure and activities. There are four ideas in element social model, for example, element, relationship, property and requirements. The substance can be alluded to as the spot, individual, occasion, idea or an article. Elements can be of two sorts, for example, string and feeble element. The feeble substance is alluded to that element which does not have its own characteristic as an essential key. The understudy, course, staff, educator or comparative items that can have set of properties can be considered as element. The sole reason for the relationship is associating one trait with another. Connections can be of numerous kinds, for example, many-to-many, balanced, one-to-numerous and cash to-one.

Entity Supertypes and Subtypes and their Types
Employees, faculty, and campus are the supertype explored on this Sun Valley database system case. Employees will have ‘professors, administrators, and staffs’ as subtypes. Faculty have departments as the subtypes and it will have course as the discriminators. Campus will have building as the subtypes and it will have inheritance of room. Toward the start of structuring the calculated model, the data of the association was gathered. Understanding the contextual investigation of the chose association was critical as the business guidelines, characteristics and connections are created according to the association contextual analysis. One of the significant elements of the reasonable model is settling on choices for making the structure of the database. The primary thing that was found in this Sun Valley database system is classification of representatives. The contextual investigation portrays that the teacher can be administrator however the administrator position isn't limited to the directors as it were. The business standard related with this condition is a teacher can be administrator once. This makes the connection between the executive and teacher balanced and flexibility is zero. Educator can be likewise the senior member of the workforce. The business guideline is that one teacher can be dignitary as a personnel at any given moment. Teachers will be leader of the office. No one but teacher can be leader of the office yet not all educators are qualified for getting to be H.O.D. All teachers will tech in a solitary division.

What are Relationships Between Sun Valley database system Entities and Cardinalities?
The identified entities for the Sun Valley database system database include ‘employees, professors, administrators, staffs, faculties, dean, department, student, course, campus, room and building.’ There must be no or more employee, and is with its all subtypes. There will be only one dean and one professor could be the dean so one professor can only be a dean. There are one or more administrators and with a condition between professor and administrator; one or more professor can be one administrator. There are one or more department that will have a relation with courses with one or more condition considering one or more department can have one or more courses. One student can belong to a single department and hence, the relation between student and department will be one-to-one. One and only one staff member can be related to one or many students. One class will be related to only one room. One and only one campus will have one or more buildings and one or more building can be related to one or more rooms. Just a single many-to-numerous relationship exists in the database, among understudy and the classes. As understudy can pick course under just a single office, regardless of how much classes the understudy visits, every one of that classes must be related with one single course. The Sun Valley database system assigns each staff as the counselor to the understudies. One staff can exhort numerous understudies however a solitary understudy will get just a single counsel. Each class is led distinctly in one room and each room is situated in a solitary structure.

Entity Primary, Candidate, and Surrogate Keys

Primary keys: empID (unique number defining employees), courseID (defining unique course), classID (defining unique classes for the students), studentID (defining unique students), roomID (defining unique rooms).

Candidate keys:
Part 2. Logical Model

Data Attributes and Their Types
Employees: empID (key attribute), first and last names (Composite attribute), and a hire date (stored attributer)

Professors: inherit employees, speciality (multi valued attribute), rank (multi valued attribute) and administrator

Administrators: inherit employees, and admin position (multi valued attribute)

Staffs: inherit employees and levels (atomic attribute)

Faculties: facultyID(key attribute) and facultyName (Composite attribvute)

Dean: inherit employess, and professorID (key attribute)

Department: inherit faculty, depatmentID (key attribute) and departmentName (Composite attribvute)

Student: studentID (key attribute), first and last names (Composite attribvute), email, empID

Course: inherit department, courseID (key attribute), Title (compound attribute), Description and credits

Campus: BuildingID (key attribute), room (atomic attribute)

Room: roomID (key attribute), type (multi valued attribute)

Building: Building ID (key attribute)

The database the executives frameworks has different information types for putting away various sorts of information in the database. So as to store the string information, database the board framework offers scorch, varchar and numerous other information types. The fundamental distinction between the scorch and varchar is that burn is a fixed length string yet varchar is a variable length string. To store numbers INT, decimal, twofold and more characteristics identified on the Sun Valley database system.

Foreign Keys
empID is the foreign key for the staffs, professors, and administrators.

One-to-One Relationships
Professor to dean, student and advisor as there will be only one advisor for each student The idea of remote key was produced for effectively associating every one of the substances in the database. The remote key is a quality in the database that alludes to just essential key of another table. The remote key is made when the database is disintegrated. The standardization coordinates the idea of remote key imperatives for actualizing connection between substances. The fundamental thought of standardization is disintegrating the tables into littler tables with the goal that the Sun Valley database system database abnormalities can be forestalled. In any case, deteriorating the tables implies that isolated characteristics will never again be identified with one another.

Conceptual Model Redesign of the Sun Valley database system
The Sun Valley database system database enables the fashioner to make, alter and erase a remote in the database. It isn't basic to announce an outside key at the season of making the table. The remote keys can be added later to the database. In the event that a database element has more than property as essential key, known as composite essential key, the database can utilize just a subset or single characteristic of essential key as outside key. Different referential moves can be made by the database dependent on the activity indicated at the season of making the outside key. These activities are course, limit, no activity, set invalid and set default. The course activity particulars that the line that alludes to the erased essential key of parent table will likewise be erased. This is connected for the update. The limit activity takes after its name. It doesn't enable the database to erase or refresh the parent push if that particular essential key is accessible in the tyke element. 'No Action' is connected when the designer needs the tyke column to be unaffected when the parent line is erased or refreshed.

The principal outside key utilized in the database is the educator essential key. The teacher can be administrator that is the reason the essential key of educator is utilized as the remote key in overseer substance. The teacher substance essential key is likewise utilized in the staff and office tables as senior member and leader of the office. In any case, every staff and division will have a solitary senior member and leader of the office, not all the teacher records will be chosen for making the outside key. In addition, the office id will likewise be utilized as outside key in the educator table on the grounds that every teacher will work under a solitary division. Every educator will have a remote key of office table. A solitary office gives numerous courses to the understudies. Division and course will have many-to-numerous connection. This infers each course will have a division id as outside key. This is on the grounds that utilizing course id in the office substance will cause redundancy of same division push for course under it. Course have many-to-one connection among those. This additionally confine the database to utilize course id in the class element as outside key. This remote key will have course activity as though the parent course will be erased then the classes won't be required. The equivalent pursue for the office course connection. This is on the grounds that, the school might need to move a few courses to different divisions if the office is erased.

There is an issues in the class and understudy elements. As understudy has many-to-numerous connection with the class substance, it is fundamental to execute a scaffold table to break down the many-to-numerous connection into one-to-numerous and many-to-one relations. This extension table is known as the understudy enlistment. This substance has understudy and class element's essential key as remote keys. These two outside keys are likewise utilized as made essential key. This composite essential key does exclude any quality of this table. That is the reason this extension table is feeble element. Every understudy will get a solitary staff as guide which infers that staff id must be utilized as the outside key in understudy table. Every understudy will have an outside key worth. Each class has a room id and each room has building number as outside key. Taken for instance, if a database has understudy and class related traits in single table. After second standardization, two separate tables are made which holds understudy and class traits. Despite the fact that the oddities are absent at database but rather the database can't comprehend which understudy is concentrating in which class. So as to forestall this circumstance, the database originator utilizes the idea of remote key limitation. In view of the relationship type, the outside key is picked. Assume, an understudy can be in just class and one class has numerous understudies on the Sun Valley database system. Along these lines, on the off chance that understudy id is considered as outside key, at that point for each understudy in the class, the class essential key must be rehashed. Sun Valley database system assignments are being prepared by our database assignment help experts from top universities which let us to provide you a reliable assignment help online service.

Bibliography

Di Luca, M., Maccari, G., Maisetta, G., and Batoni, G. (2015). BaAMPs: The database of biofilm-active antimicrobial peptides. Biofouling. https://doi.org/10.1080/08927014.2015.1021340

Erwin, D. (2015). Industrial chemical process design. Statewide Agricultural Land Use Baseline 2015. https://doi.org/10.1017/CBO9781107415324.004

Frazer, K., Callinan, J. E., Mchugh, J., van Baarsel, S., Clarke, A., Doherty, K., and Kelleher, C. (2016). Legislative smoking bans for reducing harms from secondhand smoke exposure, smoking prevalence and tobacco consumption. Cochrane Database of Systematic Reviews. https://doi.org/10.1002/14651858.CD005992.pub3

Gilson, M. K., Liu, T., Baitaluk, M., Nicola, G., Hwang, L., and Chong, J. (2016). BindingDB in 2015: A public database for medicinal chemistry, computational chemistry and systems pharmacology. Nucleic Acids Research. https://doi.org/10.1093/nar/gkv1072

IEA. (2015). World Energy Outlook 2015 - Electricity Access Database. World Energy Outlook.

Iheozor-Ejiofor, Z., Worthington, H. V., Walsh, T., O’Malley, L., Clarkson, J. E., Macey, R., … Glenny, A. M. (2015). Water fluoridation for the prevention of dental caries. Cochrane Database of Systematic Reviews. https://doi.org/10.1002/14651858.CD010856.pub2

Kranke, P., Bennett, M. H., Martyn-St James, M., Schnabel, A., Debus, S. E., and Weibel, S. (2015). Hyperbaric oxygen therapy for chronic wounds. Cochrane Database of Systematic Reviews. Sun Valley database system. https://doi.org/10.1002/14651858.CD004123.pub4

Liddle, S. D., and Pennick, V. (2015). Interventions for preventing and treating low-back and pelvic pain during pregnancy. Cochrane Database of Systematic Reviews. https://doi.org/10.1002/14651858.CD001139.pub4

Lourenço, J. R., Cabral, B., Carreiro, P., Vieira, M., and Bernardino, J. (2015). Choosing the right NoSQL database for the job: a quality attribute evaluation. Journal of Big Data. https://doi.org/10.1186/s40537-015-0025-0

MacArthur, J., Bowler, E., Cerezo, M., Gil, L., Hall, P., Hastings, E., … Parkinson, H. (2017). The new NHGRI-EBI Catalog of published genome-wide association studies, Sun Valley database system (GWAS Catalog). Nucleic Acids Research. https://doi.org/10.1093/nar/gkw1133

Pillai Riddell, R. R., Racine, N. M., Gennis, H. G., Turcotte, K., Uman, L. S., Horton, R. E., … Lisi, D. M. (2015). Non-pharmacological management of infant and young child procedural pain. Cochrane Database of Systematic Reviews. https://doi.org/10.1002/14651858.CD006275.pub3

Riemann, D., Hertenstein, E., and Schramm, E. (2016). Mindfulness-based cognitive therapy for depression. The Lancet. https://doi.org/10.1016/S0140-6736(16)00660-7

Get Top Quality Assignment Help and Score high grades. Download the Total Assignment help App from Google play store or Subscribe to totalassignmenthelp and receive the latest updates from the Academic fraternity in real time.