Hi, I need help with this project , thanks!

Subject:

2024 Summer – Database Systems (ITS-538-M80) – Full Term

Course Project Phase 2

Attached Files:

Course Project Ph2 VIDEO.mp4 (170.056 MB)

Summary:

Produce an ER Model for the following scenario (presented in Step 1 below) and business need, for the Pythagoras Math Academy.  Reference the attached video (Course Project Ph2 Video.mp4), for a full introduction and demonstration and expectations for this project.

Deliverables:

ER Model for the scenario presented.

You have the option to use MS Visio (if you have the software) to produce your ER Model.

Alternatively, if you do not have MS Visio, you can use the ERD PLUS (Open source ER Modeling Software) and produce your ER Model.

Required Software:

MS Visio by Microsoft OR

ERD PLUS, Open Source Offering. This offering is cloud-based, and you do not need to download any clients. You will need to create an account (username and password), link to this offering follows:

https://erdplus.com

 –

https://erdplus.com/#google_vignette

Lab Steps:

Step 1: Read and understand the Scenario and Business needs presented below. Define and document the ENTITIES, and RELATIONSHIPS, required to address the business needs. The nouns in the scenario below will give the ENTITIES that you will need. Finally, the verbs will describe the RELATIONSHIPS.

Scenario and Business Need

The Pythagoras Math Academy is in need to develop a database to keep track and control of his students, teachers, and classes offered.

Teachers are able to teach multiple courses. But they are required to be certified on the course they are teaching. For example, only a group of teachers are certified to teach Algebra, and other Geometry.

Students can take multiple courses. Courses are taught by teachers with the proper certification and have multiple certs as needed.  Courses are taught in one of the classrooms at one of the School’s several sites.

Each classroom is optimized to serve the needs for a course (for example some courses might require computer labs and whiteboards). The school needs to keep track of the math courses delivered in each of the sites.

Step 2: For this project, you are required to use MS Visio or ERD PLUS to produce the deliverables.

If you are using MS Visio follow the steps below:

Reference the

Course Project Ph2 VIDEO.mp4

, for a short demonstration and tutorial of this tool, and project overview/ expectations.

Open MS Visio

Click on the Software Database Template in the main window

Select the Database Model Diagram Template to open a new file.

Save the File and ensure to have in your file name PROJECT2_LastName_FistInitial.

If you are using ERD PLUS.

Watch the recorded short tutorial for this tool. Reference the attached video as follows: Course Project Ph2 VIDEO.mp4.

From the ERP PLUS main interface, select DOCUMENTS –> NEW DIAGRAM (NAME YOUR DIAGRAM) –>ER DIAGRAM –> CREATE

Reference demonstration video for how to navigate tool, how to create entities, add attributes, relationships, and Cordiality. Demonstration video attached as follows: Course Project Ph2 VIDEO.mp4

Step 3: Define and Database ENTITIES

Add an entity for each Table you identified in the Scenario and Business Need.

You will use the MS Visio tool, or the ERD PLUS open source offering to complete this step.

Step 4: ADD the required ENTITY ATTRIBUTES

Add the required Attributes for each Entity that you feel would help to define each Entity as needed.

Select one of the attributes to be PRIMARY KEY (PK)

Step 5: Define RELATIONSHIPS

Use the Crow’s Feet Notation.

Reference recorded demonstrations on how to use these notations, for MS Visio and ERD PLUS. Reference attached video as follows: Course Project Ph2 VIDEO.mp4

Drag and drop (draw) relationships between the entities you defined.

Connect the ends to the two entities for each relation.

Defined the Primary Key for the parent entity.

Step 6: Define CARDINALITY RELATIONSHIP

Set the cardinality for each of your relationships defined in Step 5.

For MS Visio: select a relationship line in the drawing area that is connecting two entities. In the Database Properties window, select the miscellaneous category. Select the cardinality for the selected relationship.

Step 7: Upload your Work

Save the File and ensure to have in your file name PROJECT2_LastName_FistInitial.

Fundamentals of Database Systems
Seventh Edition
Chapter 12
Object and ObjectRelational Databases
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Learning Objectives
12.1 Overview of Object Database Concepts
12.2 Object-Relational Features
12.3 Object Database Extensions to SQL
12.4 ODMG Object Model and the Object Definition
Language ODL
12.5 Object Database Conceptual Design
12.6 The Object Query Language OQL
12.7 Overview of the C++ Language Binding
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Object and Object-Relational Databases
• Object databases (ODB)
– Object data management systems (ODMS)
– Meet some of the needs of more complex
applications
– Specify:
▪ Structure of complex objects
▪ Operations that can be applied to these objects
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Overview of Object Database Concepts (1 of 2)
• Introduction to object-oriented concepts and features
– Origins in OO programming languages
– Object has two components:
▪ State (value) and behavior (operations)
– Instance variables (attributes)
▪ Hold values that define internal state of object
– Operation is defined in two parts:
▪ Signature (interface) and implementation (method)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Overview of Object Database Concepts (2 of 2)
• Inheritance
– Permits specification of new types or classes that
inherit much of their structure and/or operations from
previously defined types or classes
• Operator overloading
– Operation’s ability to be applied to different types of
objects
– Operation name may refer to several distinct
implementations
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Object Identity, and Objects Versus
Literals
• Object has Unique identity
– Implemented via a unique, system-generated object
identifier (OID)
– Immutable
• Most OO database systems allow for the representation
of both objects and literals (simple or complex values)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Complex Type Structures for Objects and
Literals (1 of 2)
• Structure of arbitrary complexity
– Contain all necessary information that describes
object or literal
• Nesting type constructors
– Generate complex type from other types
• Type constructors (type generators):
– Atom (basic data type – int, string, etc.)
– Struct (or tuple)
– Collection
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Complex Type Structures for Objects and
Literals (2 of 2)
• Collection types:
– Set
– Bag
– List
– Array
– Dictionary
• Object definition language (ODL)
– Used to define object types for a particular database
application
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.1 Specifying the Object Types
EMPLOYEE, DATE, and DEPARTMENT
Using Type Constructors
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.2 Adding Operations to the Definitions
of EMPLOYEE and DEPARTMENT
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Encapsulation of Operations (1 of 2)
• Encapsulation
– Related to abstract data types
– Define behavior of a class of object based on
operations that can be externally applied
– External users only aware of interface of the
operations
– Can divide structure of object into visible and hidden
attributes
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Encapsulation of Operations (2 of 2)
• Constructor operation
– Used to create a new object
• Destructor operation
– Used to destroy (delete) an object
• Modifier operations
– Modify the state of an object
• Retrieve operation
• Dot notation to apply operations to object
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Persistence of Objects
• Transient objects
– Exist in executing program
– Disappear once program terminates
• Persistent objects
– Stored in database, persist after program termination
– Naming mechanism: object assigned a unique name
in object base, user finds object by its name
– Reachability: object referenced from other persistent
objects, object located through references
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.3 Creating Persistent Objects by
Naming and Reachability
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Type (Class) Hierarchies and Inheritance (1 of 3)
• Inheritance
– Definition of new types based on other predefined
types
– Leads to type (or class) hierarchy
• Type: type name and list of visible (public) functions
(attributes or operations)
– Format:
▪
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Type (Class) Hierarchies and Inheritance (2 of 3)
• Subtype
– Useful when creating a new type that is similar but not
identical to an already defined type
– Subtype inherits functions
– Additional (local or specific) functions in subtype
– Example:
▪ EMPLOYEE subtype-of PERSON: Salary,
Hire_date, Seniority
▪ STUDENT subtype-of PERSON: Major, Gpa
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Type (Class) Hierarchies and Inheritance (3 of 3)
• Extent
– A named persistent object to hold collection of all
persistent objects for a class
• Persistent collection
– Stored permanently in the database
• Transient collection
– Exists temporarily during the execution of a program
(e.g. query result)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Other Object-Oriented Concepts
• Polymorphism of operations
– Also known as operator overloading
– Allows same operator name or symbol to be bound to
two or more different implementations
– Type of objects determines which operator is applied
• Multiple inheritance
– Subtype inherits functions (attributes and operations)
of more than one supertype
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Summary of Object Database Concepts
• Object identity
• Type constructors (type generators)
• Encapsulation of operations
• Programming language compatibility
• Type (class) hierarchies and inheritance
• Extents
• Polymorphism and operator overloading
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Object-Relational Features: Object DB
Extensions to SQL
• Type constructors (generators)
– Specify complex types using UDT
• Mechanism for specifying object identity
• Encapsulation of operations
– Provided through user-defined types (UDTs)
• Inheritance mechanisms
– Provided using keyword UNDER
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
User-Defined Types (UDTs) and Complex
Structures for Objects (1 of 2)
• UDT syntax:
–
– Can be used to create a complex type for an attribute
(similar to struct – no operations)
– Or: can be used to create a type as a basis for a table
of objects (similar to class – can have operations)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
User-Defined Types (UDTs) and Complex
Structures for Objects (2 of 2)
• Array type – to specify collections
– Reference array elements using []
• CARDINALITY function
– Return the current number of elements in an array
• Early SQL had only array for collections
– Later versions of SQL added other collection types
(set, list, bag, array, etc.)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Object Identifiers Using Reference Types
• Reference type
– Create unique object identifiers (OIDs)
– Can specify system-generated object identifiers
– Alternatively can use primary key as OID as in
traditional relational model
– Examples:
▪ REF IS SYSTEM GENERATED
▪
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Creating Tables Based on the UDTs
• INSTANTIABLE
– Specify that UDT is instantiable
– The user can then create one or more tables based
on the UDT
– If keyword INSTANTIABLE is left out, can use UDT
only as attribute data type – not as a basis for a table
of objects
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Encapsulation of Operations
• User-defined type
– Specify methods (or operations) in addition to the
attributes
– Format:
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.4a Illustrating Some of the Object
Features of SQL. Using UDTS as Types for
Attributes Such as Address and Phone
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.4b Illustrating Some of the Object
Features of SQL. Specifying UDT for
PERSON_TYPE
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Specifying Type Inheritance (1 of 2)
• NOT FINAL:
– The keyword NOT FINAL indicates that subtypes can
be created for that type
• UNDER
– The keyword UNDER is used to create a subtype
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.4c Illustrating Some of the Object Features
of SQL. Specifying UDTs for STUDENT_TYPE and
EMPLOYEE_TYPE as Two Subtypes of
PERSON_TYPE (1 of 2)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.4c Illustrating Some of the Object Features
of SQL. Specifying UDTs for STUDENT_TYPE and
EMPLOYEE_TYPE as Two Subtypes of
PERSON_TYPE (2 of 2)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Specifying Type Inheritance (2 of 2)
• Type inheritance rules:
– All attributes/operations are inherited
– Order of supertypes in UNDER clause determines
inheritance hierarchy
– Instance (object) of a subtype can be used in every
context in which a supertype instance used
– Subtype can redefine any function defined in
supertype
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Creating Tables Based on UDT
– UDT must be INSTANTIABLE
– One or more tables can be created
– Table inheritance:
▪ UNDER keyword can also be used to specify
supertable/subtable inheritance
▪ Objects in subtable must be a subset of the
objects in the supertable
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.4d Illustrating Some of the Object
Features of SQL. Creating Tables Based on Some of
the UDTs, and Illustrating Table Inheritance
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Specifying Relationships via Reference
• Component attribute of one tuple may be a reference to
a tuple of another table
– Specified using keyword REF
• Keyword SCOPE
– Specify name of table whose tuples referenced
• Dot notation
– Build path expressions
•
– Used for dereferencing
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.4e Illustrating Some of the Object Features
of SQL. Specifying Relationships Using REF and
SCOPE
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Summary of SQL Object Extensions
• UDT to specify complex types
– INSTANTIABLE specifies if UDT can be used to
create tables; NOT FINAL specifies if UDT can be
inherited by a subtype
• REF for specifying object identity and inter-object
references
• Encapsulation of operations in UDT
• Keyword UNDER to specify type inheritance and table
inheritance
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
ODMG Object Model and Object
Definition Language ODL (1 of 2)
• ODMG object model
– Data model for object definition language (ODL)
and object query language (OQL)
• Objects and Literals
– Basic building blocks of the object model
• Object has five aspects:
– Identifier, name, lifetime, structure, and creation
• Literal
– Value that does not have an object identifier
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
ODMG Object Model and Object
Definition Language ODL (2 of 2)
• Behavior refers to operations
• State refers to properties (attributes)
• Interface
– Specifies only behavior of an object type
– Typically noninstantiable
• Class
– Specifies both state (attributes) and behavior
(operations) of an object type
– Instantiable
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Inheritance in the Object Model of ODMG
• Behavior inheritance
– Also known as IS-A or interface inheritance
– Specified by the colon (:) notation
• EXTENDS inheritance
– Specified by keyword extends
– Inherit both state and behavior strictly among classes
– Multiple inheritance via extends not permitted
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Built-In Interfaces and Classes in the
Object Model
• Collection objects
– Inherit the basic Collection interface
•
– Creates an iterator object for the collection
– To loop over each object in a collection
• Collection objects further specialized into:
– set, list, bag, array, and dictionary
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.6 Inheritance Hierarchy for the
Built-In Interfaces of the Object Model
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Atomic (User-Defined) Objects
• Specified using keyword class in ODL
• Attribute
– Property; describes data in an object
• Relationship
– Specifies inter-object references
– Keyword inverse
▪ Single conceptual relationship in inverse directions
• Operation signature:
– Operation name, argument types, return value
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.7 The Attributes, Relationships,
and Operations in a Class Definition
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Extents, Keys, and Factory Objects
• Extent
– A persistent named collection object that contains all
persistent objects of class
• Key
– One or more properties whose values are unique for
each object in extent of a class
• Factory object
– Used to generate or create individual objects via its
operations
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Object Definition Language ODL
• Support semantic constructs of ODMG object model
• Independent of any particular programming language
• Example on next slides of a UNIVERSITY database
• Graphical diagrammatic notation is a variation of EER
diagrams
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.9a An Example of a Database Schema.
Graphical Notation for Representing ODL Schemas
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.9b An Example of a Database Schema. A
Graphical Object Database Schema for Part of the
UNIVERSITY Database (GRADE and DEGREE
Classes Are Not Shown)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.10 Possible ODL Schema for the
UNIVERSITY Database in Figure 12.8(b) (1 of 2)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.10 Possible ODL Schema for the
UNIVERSITY Database in Figure 12.8(b) (2 of 2)
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Interface Inheritance in ODL
• Next example illustrates interface inheritance in ODL
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.11a An Illustration of Interface
Inheritance Via “:”. Graphical Schema
Representation
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Figure 12.11b An Illustration of Interface
Inheritance Via “:”. Corresponding
Interface and Class Definitions in ODL
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Object Database Conceptual Design
• Differences between conceptual design of ODB and RD
B, handling of:
– Relationships
– Inheritance
• Philosophical difference between relational model and
object model of data
– In terms of behavioral specification
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Mapping an EER Schema to an ODB
Schema (1 of 2)
• Create ODL class for each EER entity type
• Add relationship properties for each binary relationship
• Include appropriate operations for each class
• ODL class that corresponds to a subclass in the EER
schema
– Inherits type and methods of its superclass in ODL
schema
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Mapping an EER Schema to an ODB
Schema (2 of 2)
• Weak entity types
– Mapped same as regular entity types
• Categories (union types)
– Difficult to map to ODL
• An n-ary relationship with degree n > 2
– Map into a separate class, with appropriate
references to each participating class
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
The Object Query Language OQL
• Query language proposed for ODMG object model
• Simple OQL queries, database entry points, and iterator
variables
–
– Entry point: named persistent object
– Iterator variable: define whenever a collection is
referenced in an OQL query
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Query Results and Path Expressions
• Result of a query
– Any type that can be expressed in ODMG object
model
• OQL orthogonal with respect to specifying path
expressions
– Attributes, relationships, and operation names
(methods) can be used interchangeably within the
path expressions
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Other Features of OQL (1 of 2)
• Named query
– Specify identifier of named query
• OQL query will return collection as its result
– If user requires that a query only return a single
element use element operator
• Aggregate operators
• Membership and quantification over a collection
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Other Features of OQL (2 of 2)
• Special operations for ordered collections
• Group by clause in OQL
– Similar to the corresponding clause in SQL
– Provides explicit reference to the collection of objects
within each group or partition
• Having clause
– Used to filter partitioned sets
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Overview of the C++ Language Binding in
the ODMG Standard
• Specifies how ODL constructs are mapped to C++
constructs
• Uses prefix d_ for class declarations that deal with
database concepts
• Template classes
– Specified in library binding
– Overloads operation new so that it can be used to
create either persistent or transient objects
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Summary
• Overview of concepts utilized in object databases
– Object identity and identifiers; encapsulation of
operations; inheritance; complex structure of objects
through nesting of type constructors; and how objects
are made persistent
• Description of the ODMG object model and object query
language (OQL)
• Overview of the C++ language binding
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved
Copyright
Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved