1	Accessing the Oracle DB Oracle db location is on: www.it.scps.nyu.edu Access using telnet: Start button, select Run, type in telnet, select Connect from menu bar, select Remote System, type in the Oracle db location at the host name, and then Connect. It may take up to 30 seconds before host responds. At Login, type the username you were given in class. At Password, type in the password you were given in class. If asked about a SID type in int. If you were not asked to enter a SID, then at the prompt type in as follows, then enter int into SID request. $ ksh <enter> $. .profile <enter>
2	Accessing the Oracle DB Access using ssh: Go to www.ssh.com and download a windows client. Install windows client on your locale computer. Launch ssh terminal and the hit enter. Input the host name: www.it.scps.nyu.edu Input in your userid and hit enter. Then choose ok. At Password prompt, type in the password you were given in class and hit okay. If you were not asked to enter a SID, then at the prompt type in as follows, then enter int into SID request. $ ksh <enter> $. .profile <enter>
3	Accessing the Oracle DB Join Oracle technet. Go to http://technet.oracle.com and create an account. Download and install on your local computer OEM for Windows. Once installed to gain access start DBAStudio At hostname type www.it.scps.nyu.edu Leave port at 1521 At SID enter: INT Then okay.
4	Using Unix Unix does use \ it uses / to separate on directory from another. Copying files cp <name of file to be copied> <path to and/or name of file to be copied to> cp oracle1.sql oracle2.sql #same directory cp oracle1.sql ../oracle2.sql #to a directory above cp oracle1.sql ../../oracle2.sql #to two directories above Cp oracle1.sql /next/oracle2.sql #to a directory below Deleting files rm <path to and/or name of file to be copied to> rm oracle1.sql #same directory rm ../oracle1.sql #from directory above rm /next/oracle1.sql # from a directory below
5	Using Unix Changing directories cd ../ #takes you up one directory cd /next # takes you down one directory cd ../../ #takes you up two directories cd /next/next #takes you down two directories Editing files Many editors available, most widely used pico Works like word perfect, simple text based. vi Vim emacs Another editor Making Directories mkdir <directory_name> Can only make directories in your own home directory
6	Using Unix Removing Directories rmdir <directory_name> Can only remove directories in your own home directory Uploading to/downloading from the server Requires that you obtain an FTP client. FTP once loaded will ask you for the host name, username and password. This gives you direct access to your home directory. Home directory Located in the /home directory. You have complete control over this area of the server.
7	Using Unix Other important directories in your home directory web directory This is where you can place web pages that can be accessed remotely The URL would be http://www.it.scps.nyu.edu/~username/ cgi-bin directory This is where you can place server side includes and other scripts that you want to access remotely. The URL would be http://www.it.scps.nyu.edu/cgi-bin/username/
8	Creating a User in Oracle Once you have successfully logged you can access the sql engine by typing: sqlplus Next, type in for username and password, system/manager. Next, at the sqlplus prompt type in the following command: CREATE USER <username> IDENTIFIED BY <password> DEFAULT TABLESPACE data QUOTA 8M ON data TEMPORARY TABLESPACE temp Next the user must be granted privileges to access and use the int database and their tablespace. So, next we type the following: GRANT RESOURCE, CONNECT TO <username>
9	Database Design Phase I: Conceptual database design concerned with analyzing organizational and user data requirements. Then formalizing the data requirements with a conceptual graphical model. Phase II: Selection of a logical data model, usually relational, which will be used to implement the conceptual model. Phase III: Physical database design concerned with the where and how data will be stored and accessed and instituting structure and control on the data model and on data access.
10	Phases of Database Design Conceptual database design Analyze user data requirements Construct ER model Logical database design Select logical data model Construct data repository Physical database design Identify storage & location Performance tuning
11	Phase I: Conceptual database design Analyze user data requirements. What data is important Identify entities Identify attributes Identify their relationships Construct entity relationship diagrams. Sometimes construct enterprise-wide data model.
12	Phase II: Logical Database Design Select a logical data model which will be used to implement the conceptual model: network, object-oriented, hierarchical, and relational. Develop a data repository: a catalog of all the data items & their characteristics. Normalize and assign attributes to tables to reduce data redundancy, eliminate data anomalies, and facilitate data access.
13	Phase III: Physical Database Design Decision on how the logical model and data repository description can be represented in computer hardware; that is where and how entities, attributes, and relationships will be stored and accessed Determine: storage media used, where records stored access methods to retrieve data (sequential, direct, indexes, lists).
14	Strategic Data Planning & Information Engineering They combine corporate strategy with database design and systems analysis. They focus on exploiting database design opportunities.
15
16	Contemporary Systems Development Strategic requirements analysis. Systems analysis. Systems design. Detailed design Systems implementation.
17	Strategic Requirements Analysis: Determine future strategic emphasis. Align information systems planning with organization strategic plan. Construct enterprise-wide data model. Construct high level process model. Assess areas for technological improvement and exploitation.
18	Contemporary Systems Analysis Analyze user data requirements. Refine and expand enterprise-wide data model to incorporate logical views. Begin constructing data repository. Model processes using DFDs. Integrate data and process models.
19	Contemporary Systems Design Input form design. Report design. Screen design. Select DBMS implementation: hierarchical, network, relational, etc. Design database repository. Plan for implementation & training.
20	Contemporary Detailed System Design Select storage media. Determine access methods. Procedure and task design. Program and system design. Configure hardware. Configure data communications network
21	Contemporary Systems Implementation & Maintenance Code. Test. Install. Correct problems. Enhance features. Audit system.
22	Underlying Theme of Contemporary Systems Development Incorporate ideas from reengineering, downsizing, total quality management, outsourcing, and information engineering into the systems development process. Information technology development should be aligned with organizational strategy.
23	Communication Reconciles World Views
24	Entity Relationship Method (ER) A conceptual data modeling approach used to understand and simplify data relationships. Derived from the Semantic School of knowledge modeling. The concepts are used by linguists to represent the meaning of sentences and words. The concepts are used by artificial intelligence researchers to represent and model knowledge.
25	Three Basic Graphical Symbols in an ER Diagram Rectangles are used to model conceptual data units or data objects. Circles are used to model attributes. Attributes are the characteristics, components or properties of entities. Diamonds are used to model the structural associations that exist between entities.
26	ER Attribute Diagram
27	More Terminology Object: things in the real world that can be observed and classified because they have related properties Entity: the groupings we use when we categorize the objects. Sometimes called a class.
28	More On Entities Entities can be tangible Person Building Entities can be intangible Employee benefit plan Transaction
29	Semantic Network Concept The meaning of a word can be determined by its relationship to other words.
30	Semantic Network Structural Primitives Three “Structural Primitives” form the basis of semantic network modeling: “is-part-of” how something can be constructed from components. “isa” how an object “inherits” characteristics of a general class of objects. “is-associated-with” how an object relates to or interacts with other entities.
31	Relationships That Can Exist Between Entities & Attributes Aggregation abstraction (is-part-of): Constructed from components consisting of attributes and other entities. Generalization & specialization abstraction (isa): A subclass of a more general entity which inherits attributes of the superclass entity in combination with its own unique attributes. Association abstraction (is-associated-with): Can interact with other entities and affect the attribute values of other entities.
32
33
34	Composite & Simple or Atomic Attributes Composite Attribute (Group Attributes) Combining or aggregating related attributes An “address” attribute, made up of a “city”, “state”, and “zip” attribute Simple or “Atomic” Attributes Attributes that are not divisible into any simpler data unit
35	Multi-valued Attribute A single attribute that can have multiple values. An individual may have more than just one telephone number or job skill. A way to accommodate multi-valued attributes is to elevate them to the status of entity.
36
37	Attribute Domain Domain of an Attribute: all the possible legal values or categories permitted to the attribute.
38	Null Values A null is an attribute without a value. How can a null value occur? The value is not known The attribute is not relevant to describe that particular entity A primary goal of relational database design is to minimize the occurrence of null values.
39	Entities and the “is-part-of” Aggregation Structure Entities can be combined to form aggregate entities. The “is-part-of” structure describes the aggregation of entities. “is-part-of” is represented as a relationship on an ER diagram using the diamond symbol.
40	The “is-part-of” Aggregation Structure In practice the “is-part-of” label is often replaced with a synonym such as: has, contains, includes, consists-of, embodies, encompasses, incorporates, component-of, holds, comprised-of, segment-of, embraces, piece-of, member-of, element-of.
41	Component Entities Aggregated to Create a Computer Entity
42	Department Entities Aggregated to Create a Division Entity
43	Entities and the “isa” Generalization-Specialization Structure Entities can have subtypes or subclasses and supertypes or superclasses The relationship between an entity subtype and its parent is referred to as an “isa” relationship
44	Entities and the “isa” Generalization-Specialization Structure On an ER diagram an “isa” relationship connecting the more specialized entities to the generalized entity is depicted either as: An upside down triangle. With a diamond with the “isa” label placed inside
45	Entities and the “isa” Generalization-Specialization Structure The supertype entity is a generalization of the subtype “specialization” entities. Each subtype entity inherits attributes from the supertype entity. Each super type entity has unique attributes of its own.
46
47	Bank Isa Structure
48	Entities and the “is-associated-with” Structure Sometimes two entities have very few attributes in common but engage in transactions with each other For example, an employee assigned to a car. These transactions are modeled in an ER diagram by the “is-associated-with” structure.
49
50	Entities and the “is-associated-with” Structure This structure models relationships associated with change, where entities have engaged in transactions with each other and where attribute values have changed. “is-associated-with” is often represented on an ER diagram with verbs such as: obtain, place, order, diagnose, use, request, register, initialize, assigned.
51	Entities and the “is-associated-with” Structure Sometimes the ‘is-associated-with” label uses an abbreviation made up of the names of the two entities that are being related. A relationship between a customer entity and a loan entity may be described as the “cust_loan” relationship.
52
53	Elevating Attributes to Entities How to test for deciding whether an attribute should be considered an entity: When the attributes associated with it become important in their own right. Color might be an attribute to describe your bicycle but to a paint company it would be an entity in its own right consisting of the types of dyes and the amount of dyes necessary to produce the color. An attribute should almost always be considered an entity if it is multi-valued.
54	Cardinality The relationship between 2 entities can be described in terms of “cardinality.” It is the maximum number of records in one file that are linked to a single record in another file and vice versa.
55	How to Express Cardinality One to one 1:1 An employee can be assigned to 1 car. One to many 1:N A client can check out several videos. Many to many N:M A student can take many courses and a course may be taken by many students.
56	The Most Important Thing to Keep In Mind About Cardinality Cardinality is determined by the “business rules” created by the client organization. The users and the organizational documentation tell you what cardinality exists among entities and attributes.
57
58
59	One-to-Many and Many-to-Many
60	Relationships Can Have Upper and Lower Bounds A faculty member can teach 0 to 4 courses (lower bound of 0 & upper bound of 4) and a single course can be taught by 0 to 1 faculty members (lower bound 0 & upper bound 1)
61	Upper and Lower Cardinality Bounds When the lower bound for cardinality is 0 the relationship is referred to as “optional.” When the lower bound for cardinality is 1 the relationship is referred to as “mandatory.”
62	Recursive Relationships This occurs when an entity participates in a relationship with itself. Recursive relationships can also have upper and lower bounds. Example: An organization has an “Employee” entity & wants to keep track of employees who are married to each other using this entity. This is a 1:1 recursive relationship where the “Employee” is related to itself.
63	One-to-One Recursive Relationship
64	Upper and Lower Bounds for 1:1 Recursive Relationship
65	Converting ER Diagrams to Relational Tables The ER Diagram is a highly abstract conceptual representation of organizational data. ER is independent representation of how the database is implemented. Can support relational, hierarchical, network or object-oriented databases.
66	Relational Database Model Relational database model is also a conceptual model that is: More restrictive. Lower level. Requires all data to be in tables or relationships. Links tables using “primary keys.”
67	Schema Mapping Schema Mapping or Schema Transformation is a method to convert data from an ER diagram into relational tables. Uses cardinality to convert ER diagram into relational tables.
68	Basic Schema Mapping Rules For 1:1 cardinality: all attributes are merged into a single table. For 1:N cardinality: post the “identifier” from the “one” side as an attribute to the “many” side. For N:M cardinality: create a new table and post the primary keys from each entity as attributes in the new table.
69	Null Values Should be avoided. Make it difficult to construct & interpret relational data base queries. Create ambiguity because users are unsure whether the value is not known or the attribute in question is not applicable to the particular entity instance.
70	The Optional -Max Technique and Null Values The optional-max technique guarantees that the relational tables will have the minimum numbers of null values The optional-max method avoids exception rules when modeling “optional” relationships where the lower bound for cardinality is “0”.
71	Optional-Max Conversion Rules: Rule 1 Construct an ER diagram and label the upper and lower bounds for cardinality. All multi-valued attributes should be represented as entities.
72	Optional-Max Conversion Rules: Rule 2 In every instance where the lower bound for cardinality is 0 and the upper bound is 1, temporarily label the upper bound as N. If the upper bound is greater than 1 (for example, 3, or 10 or N), leave it as is.
73	Optional-Max Conversion Rules: Rule 3 Use the entity name for each entity as the table name.
74	Optional-Max Conversion Rules: Rule 4 If an identifier or primary key has not been selected then select an identifier for each table.
75	Optional-Max Conversion Rules: Rule 5 Take all the attributes that describe an entity & post them as column names in the relational tables.
76	Optional-Max Conversion Rules Rules 6, 7, & 8 relate only to the upper cardinality bounds. All the upper bounds greater than 1 should be treated as a “many.”
77	Optional-Max Conversion Rules: Rule 6 For 1:1 relationships, merge all the attributes into a single table, The name of the relational table can be: One of the entity names that participate in the 1:1 relationship. A composite name formed by combining the two entity names. Or a new name that reflects the meaning of the data in the table.
78	Optional-Max Conversion Rules: Rule 7 For 1:N relationships, take the identifier of the “one” side of the 1:N relationship and post it as an attribute to the “many” side. The identifier posted to the “many” side is a foreign key.
79	Optional-Max Conversion Rules: Rule 8 For N:M relationships, create a new table using the relationship name as the name for the table. The new table is called an intersection table. The table identifier is a concatenated key created by posting the primary keys from the two entities that participate in the relationship. Each identifier posted to the new intersection table is a foreign key.
80	Optional-Max Conversion Rules: Rule 9 Take any additional attributes and post them as column names in the appropriate relational table. If there are any composite attributes in the relational table definition, decompose them into simple or atomic attributes.
81
82
83
84
85
86
87
88	Converting an “Isa” Relationship into Relational Tables In Figure 3.8 Graduate Isa Student and Undergraduate Isa Student . The relationships from Student to Graduate and Student to Undergraduate have optional lower bounds of 0 and upper bounds of 1 (0:1). In the other direction, the relationships from Graduate to Student and Undergraduate to Student are mandatory relationships (if you are a graduate student you must be a student) with lower bounds of 1 and upper bounds of 1.
89	Converting an “Isa” Relationship into Relational Tables cont. Use Rule 7 of the optional-max rules where the 0:1 relationship from Student to Graduate is temporarily converted to a 0:N relationship. The identifier (StudentID) of the 1 side Student side, is then posted as an attribute on the many side, Graduate, and the StudentID becomes a foreign key in the graduate side.
90
91	Converting Aggregate Entities into Relational Tables Relationship between Computer & Motherboard and between computer and monitor is a 1:N relationship. A motherboard can exist on 0 to many computers but a computer will only have 1 motherboard. Use Rule 7 and post the identifier from the one side (monitor or motherboard) as an attribute on the table describing the many side (the computer).
92	Converting Aggregate Entities into Relational Tables Cont.. Relationship between hard drive and computer is an N:M relationship. A hard drive can be on 0 to many computers and a computer can have 0 to many hard drives. The upper limits of these relationships are both N. Use Rule 8 and create a new intersection table. The identifier of the intersection table is a concatenated key created by posting the primary keys from the two entities that participate in the relationship.
93
94	Converting One to One Recursive Relationships to Relational Tables Figure 3.10 presents an example of an organization that keeps track of employees that are married to each other. There is a 1:1 recursive relationship because an employee entity can participate in a marriage relationship with another employee entity. The marriage relationship is. however, optional An employee does not have to be married to another employee.
95	Converting One to One Recursive Relationships to Relational Tables cont. Rule 8 of the optional-max for N:M relationships is employed and a new intersection table is constructed using the SS# of each employee as the concatenated key of the new table 1:1 recursive relationships are not always easy to identify during the ER modeling process, particularly if they involve optional relationships.
96
97	Additional Examples for Converting Recursive Relationships to Relational Tables Consider an example where one individual supervises many employees but an employee has only one supervisor (1:N). Use optional-max rule 7 where a key from the “one” entity (supervisors SS#) is posted as a foreign key to the “many’ entity (employee).
98	Many-to-Many Recursive Relationships Manufacturing environments often contain N:M recursive relationships in a “bill-of-materials” relationship where a component can have many subcomponents and a subcomponent can be used in many other components. Use rule 8 for N:M relationships and create an intersection table where the identifier of each entity is combined to make a concatenated key for the new table.
99	Many-to-Many Recursive Relationship Figure
100	Binary, Ternary & N-ary Relationships Binary relationship: only 2 entities are connected to each other. Ternary relationships: 3 entities are connected to each other. N-ary relationships: 3 or more entities participate with each other in a relationship. Ternary and higher order relationships are almost always N:N:N and therefore require new intersection tables.
101
102
103
104
105
106
107
108
109
110	Disease Diagnosis Tables PATIENT (PID, NAME) PAT-SYMPTOM (PID, SID) SYMPTOM (SID, DESCRIPTION, SYMPTOM_CATEGORY {cough, temperature, itch, etc..}) ASSOCIATE (SID, DID) DISEASE (DID, DISEASE_NAME) INDICATES (DID, TID) TREATMENT (TID, TREATMENT_DESCRIPTION)
111
112
113
114
115
116
117
118
119
120