Data Modeling ISYS 464 Install Oracle 10 g
- Slides: 54
Data Modeling ISYS 464
Install Oracle 10 g Express • Website to download: – http: //www. oracle. com/technology/products/database/xe/index. html – Choose Linux or Windows version – Choose: Oracle Database 10 g Express Edition (Western European) • Installation guide: – http: //download. oracle. com/docs/cd/B 25329_01/doc/install. 102/b 25143/t oc. htm • Getting started guide: – http: //download. oracle. com/docs/cd/B 25329_01/doc/admin. 102/b 25610/t oc. htm • Two types of accounts: – System administrator: Remember the name and password used to install the Oracle. • This account lets you to create/delete user account. – User • Sample database: HR (Human Resource Database) – This database initially is “locked”. You need to login as an Administrator to unlock the HR database. – User name: HR, Password: HR
Start and Login to Oracle 10 g Exp • To start: From Oracle Pop. Up menu, choose – Start database: wait for the services to complete: • The Oracle. XETNSListener service was started successfully. • The Oracle. Service. XE service is starting. . . . • The Oracle. Service. XE service was started successfully. – Then choose: Go to database home page • Login • Accounts – System administrator – User • Logout – Stop database
Start and End My. SQL • My. SQL is installed as a service. • To start My. SQL: – Control Panel/Administrative Tools/Services/My. SQL/ start • • My. SQL Administrator My. SQL Query Browser My. SQL Command Line Client To stop My. SQL: – Control Panel/Administrative Tools/Services/My. SQL/ Stop
Database Design Process • Conceptual database design: – The process of creating a data model independent of implementation details such as the target database model and physical considerations. • Logical database design: – The process of designing database logical structure based on a specific database model (such as relational model), but independent of a particular DBMS and physical considerations. • Physical database design: – The process of implementing the database on a secondary storage.
Requirements Collection and Analysis • The process of collecting and analyzing information about the organization that is to be supported by the database system, and use this information to identify the requirements for the new system.
NBC Olympic Website • http: //www. nbcolympics. com/
Making Airlines Reservations • US Airways -http: //www. usairways. com/awa/ • Select your seats • Web check-In
Supermarket Advertisement Lucky. Market presents another great way to save with your Lucky. Market Rewards Card! You can get up to 4 FREE movie tickets! From Feb. 01 through June 9, 200 X, use your Lucky. Market Rewards Card every time you shop at Lucky. Market. When you accumulate between $250 - $399. 99 in groceries during the qualifying period, you get 2 FREE movie tickets! And if you purchase $400 or more during the same period you get 4 FREE movie tickets!
Fact-Finding Techniques • Examining documentation – Defining problem and need for database: • Internal memos, minutes of meetings, documents that describe the problem, organizational chart – Describe the current system: • Various types of flowcharts and diagrams, data dictionary, database system design, program documentation • Interviewing • Observing the enterprise in operation • Questionnaires
User Views • A user view defines what is required of a database system in terms of the data to be held and transactions to be performed on the data from the perspective of a particular job role or enterprise application area. • Identifying user views helps to ensure that no major users of the database are forgotten when developing the requirements for the new database system. • Examples of user views: – Reports, Forms, documents
Conceptual Database Design Methodology • Identify entity types. • Identity relationship types between the entity types. • Identify and associate attributes with entity or relationship types. • Determine attribute domains. • Determine candidate keys and primary key. • Validate conceptual model: – Check for redundancy, support required transactions, review the model with user
Objectives of Database design • Be able to link related records in the database. • Eliminate data duplication.
Entity-Relationship Diagram • ER modeling is a top-down approach to database design that begins by identifying the entities and relationships between entities that must be represented in the model. – Relative ease of use. – Widespread CASE tool support. – The belief that entities and relationships are natural modeling concepts in the real world. – Classifying things according to their various kinds.
ERD Models Entities and Business Rules • Example: – A customer may submit any number of orders. However, each order must be submitted by exactly one customer. – A student may register for a section of a course only if he or she has successfully completed the prerequisites for that course.
Entities • An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. – – – Person: Employee, Student, patient Place: Warehouse, Store Object: Product, Machine. Event: Registration, Sale, Renewal Concept: Account, Course • Physical existence: • Customer, student, product, etc. • Conceptual existence: • Bank accounts, sale
Entity Type • A collection of entities that share common properties or characteristics. • An entity type represents a collection of entities. • In an ERD, it is given a singular name. • Diagrammatic representation: – A rectangle labeled with the name of the entity
Entity Instance • An entity instance is a single occurrence of an entity type: – Student entity: SID, Sname, Major – Two instances of Student entity type: • S 1, Peter, Bus • S 5, Paul, Sci
Relationship Type • Relationship: Interaction between entity types. – It is an association representing an interaction among the instances of one or more entity types that is interest to the organization. • It has a verb phrase name: – Faculty teach Course, Faculty advise Student – Customer open Account, Customer purchase Product.
Figure 3 -10 Relationship types and instances a) Relationship type b) Relationship instances
Binary Relationship • A relationship involves two entity types. • Three kinds of Binary Relationship – 1: 1 – 1: M – M: M • Determined by business rules
M: M Relationship Boy Peter Mary Paul Linda John Nancy Woody Mia Alan Pia A boy may date 0, 1, or many girls. A girl may date 0, 1, or many boys. Note: “Many boys date many girls” is not a correct interpretation. Girl
1: 1 Relationship Man Woman Peter Mary Paul Linda John Nancy Woody Mia Alan Pia A man may marry 0 or 1 woman. A woman may marry 0 or 1 man.
1: M Relationship Father Peter Paul John Woody Alan A father has 1 or many children. A child has 1 father. Child Mary Brian Linda Aron Nancy Ronald Mia Pia
Cardinality Constraint • A cardinality constraint specifies the number of instances of entity type A that can (or must) be associated with each instance of entity type B. • Participation constraint – Full participation (Mandatory) – Partial participation (Optional)
Notations
Other Notations UML Notations: – 0. . 1, 1. . 1 – 0. . *, 1. . * – 3. . 5 Student Has 1. . 1 Account • Traditional: Student 1 Has 1 Account
1: 1 Relationship • Examples: – Husband, Wife – State, State Governor – Order, Invoice
1: M Relationship • Examples: – Father, Child – Department, Employee – Customer, Order
M: M Relationship • Examples: – Boy friend, Girl friend – Bank customer, Bank account – Student, Student organization
Traditional ERD Notations 1 Student M M Advise Has 1 Account M Enroll 1 Faculty 1 Teach M Course
UML ERD Notations Student Has 1. . 1 0. . * Advise 1. . 1 Account Enroll 0. . * 1. . 1 Teach Faculty 1. . 1 1. . * Course
Book Notations Has Student Account Enroll Advise Faculty 0. . * Teach Course
Other Examples • A database to record visitors and web pages they view. • An online shopping website database to record customers, orders (shopping carts) and products purchased by customers. • An auction database to record sellers and the items they sell, buyers and the items they purchase. • Supermarket advertisement
Recursive Relationship • A relationship type where the same entity type participates more than once in different roles. • Examples: – Employee – Supervise -- Employee – Student -- Tutor– Student – Faculty – Evaluate -- Faculty
Supervise Supervisor Employee Supervisee Employee 1 M Supervise
Attributes • Properties of an entity or a relationship. • Simple and composite attributes – Address: Street address, City, State, Zip. Code – Street Address: Number, Street, Apt# – Phone#: Area Code, number • Single-valued and multi-valued attributes – – Student’s Major attribute Faculty’s Degree. Earned attribute Vehicle’s Color attribute Others: Phone. Number, Email. Address • Derived attributes • Keys: Key attribute uniquely determines an entity. – Candidate key, primary key, composite key
UML Notations Student SID {PK} Sname Fname Lname Address Street City State Zip Phone[1. . 3] Sex Date. Of. Birth /Age
SID {PK} Sname( Fname, Lname) Address( Street, City, State, Zip) {Phone} Sex Date. Of. Birth [Age]
Fname SID Lname Sname Phone Date. Of. Birth Age Student
Domains of Attributes • The set of allowable values for one or more attributes. • Input validation • Examples: – Sex: F, M – Emp. Hourly. Wage: Between 6 and 300 – Emp. Name: 50 characters
Time-Dependent Attributes • Auditing • Reconstructing the database state at a point in time: – Database snapshot • Regulations: – Sarbanes-Oxley • Public Company Accounting Reform and Investor Protection Act of 2002 and commonly called SOX or Sarb. Ox; July 30, 2002) • Example: • Product. Price: PID, {Price. History(Price, Time. Stamp)}
Attributes on Relationship Online Shopping Cart CID Addr Cname Customer 1 Has Cart. ID M Date Shopping. Cart M Has M Product Price PID Pname
Order Form
Online Shopping Cart CID Addr Cname Customer 1 Has Cart. ID M Date Shopping. Cart M Qty Has M Product Price PID Pname
Attributes on Relationship • Examples: – Student/Course: Grade – Order/Product: Quantity
Student SID Enroll 0. . * Course CID Grade Student M Enroll M Grade Course
Figure 3 -11 a A binary relationship with an attribute Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationship
Figure 3 -11 b An associative entity (CERTIFICATE) Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right. Note that the many-to-many cardinality between entities in Figure 3 -11 a has been replaced by two one-to-many relationships with the associative entity.
N-ary Relationship • Doctor – Patient – Ailment • Police – Crimal – Crime • Air. Craft – Bomb – Target • Note: There is no deterministic relationship (1: 1 or 1: M) between any two of these entities.
Figure 3 -12 Examples of relationships of different degrees (cont. ) c) Ternary relationship Note 1: a relationship can have attributes of its own. Note 2: This ternary relationship exists only if there is no binary relationship between these three entities.
Problems with ER Models Connection Traps • Fan traps: Where a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous Has Staff 1. . * 1. . 1 Division Oversees 1. . 1 Which branch does Peter work? Has Oversees Branch Division 1. . 1 1. . * Branch Staff
Entity Type not System User or Organizational Unit Report My. Company Send. To Has Me Department Note: An entity type represents a collection of entities.
Figure 3 -21 Examples of multiple relationships a) Employees and departments Entities can be related to one another in more than one way Example: Auction site: User and Auction Item
- Isys 464
- Isys 464
- B logic isys
- Isys 464
- Isys 263
- Isys intelligent systems
- Isys 263
- B logic isys
- Helen c erickson
- Dimensional modeling vs relational modeling
- Data warehouse modeling best practices
- Wees stil voor het aangezicht
- O heer my god lied 464
- Mil std 461 464
- Kin 464
- Industrial information system
- Halal
- Sql certification microsoft
- Kin 464
- Cse 464
- Modeling relational data with graph convolutional networks
- Data modeling techniques
- Data modeling using entity relationship model
- Data warehouse modeling tutorial
- Modeling data in the organization
- Modeling data in the organization
- Qlik sense data model best practices
- Vhdl data flow modeling
- Oltp data model
- Sinusoidal functions as mathematical models
- Modeling data distributions
- Er modeler
- Data flow modeling in verilog examples
- Data vault pros and cons
- Describe data and process modeling concepts and tools
- Modeling data in the organization
- Chapter 2 modeling distributions of data
- Btm 382
- Er diagram of a company
- When is the concept of a weak entity used in data modeling
- Chapter 2 modeling distributions of data
- Data organization
- Data modelling
- Erwin이란
- Tbone codd
- Modeling distributions of data
- Modeling data in the organization
- Cardinality and modality in database
- Typical process description tools include
- Oracle maximum availability architecture
- Dave moore highwater site: linkedin.com
- Oracle data guard far sync
- Sql
- Oracle master data
- Maintaining