1 / 49

A E-R Model for Online Flower Store

A E-R Model for Online Flower Store. Occasion code. Product Code. Description. description. Name. Unit price. Occasion. address. Picture. Products. N. Receivers. 1. M. M. 1. Customer ID. Orders. 1. Order Date. M. M. address. Order Number. Order lines. Buyers . M.

shyla
Download Presentation

A E-R Model for Online Flower Store

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A E-R Model for Online Flower Store Occasion code Product Code Description description Name Unit price Occasion address Picture Products N Receivers 1 M M 1 Customer ID Orders 1 Order Date M M address Order Number Order lines Buyers M 1 Credit card Customer ID Product Code Name Order Number quantity

  2. A Simplified E-R Model Description Product Code Occasion code Unit price Occasion Picture Receiver Name Products N M Receiver address 1 Order Number Credit card # M Order lines Order Date M 1 Orders address M Customer 1 Password Product Code Order Number Customer Name quantity Phone Number

  3. Simplified Tables • PRODUCTS • Product_Code (Primary Key) • Product_Name • Unit_Price • USAGE • Product_Code (Concatenated Key) • Occasion (Concatenated Key) • CUSTOMER • Customer_Name(Primary Key) • Password • Address • Phone_Number

  4. Tables • ORDERS • Order_Number (Primary Key) • Order_Date • Customer Name (Foreign Key) • Receiver Name • Receiver Address • Credit_Card_Type • Credit_Card_Num • Credit_Card_Expiry • ORDERLINE • Order_Number (Concatenated Key) • Product_Code (Concatenated Key) • Quantity

  5. Functional Dependence • PRODUCT (3NF) Product_Code  Product Name,Unit Price • USAGE (BCNF) • CUSTOMER (3NF) CustomerID  Name, Address, password, phone number • ORDERS (2NF) Order Number  CustomerID, Order Date, Receiver Name,… CreditCardNum  CreditCardExpiry (transitive dependence) Receiver Name  Receiver Address (transitive dependence) • ORDERLINE (3NF) • ProductNumber + OrderNumber  Quantity

  6. Transaction Processing

  7. Transaction • A transaction is a sequence of steps that constitute some well-defined business activities • Transaction boundaries - the logical beginning and end of transactions • Commit changes for successful transactions • Reject changes for aborted transactions

  8. Embedded SQL • SQL can be embedded in host languages : PL/I, COBOL, etc. • SQL statements are prefixed by EXEC SQL • Use EXEC SQL DECLARE to define tables, views, and cursors • In SQL, use prefix “:” to reference host variables • Return feedback information through SQLCA

  9. SQLCA • SQLCA SQL Communication Area SQLCODE 0 success, +100 no data found, <0 errorEXEC SQL INCLUDE SQLCA.....IF SQLCODE < 0 THEN.... • Reference: C.J. Date, A Guide to DB2 , ch.10-11.

  10. DCL GIVENS# CHAR(5) ; DCL RANK FIXED BIN(15) ;DCL CITY CHAR(15) ;EXEC SQL DECLARE S TABLE (S# CHAR(5) NOT NULL, SNAME CHAR(20), STATUS SMALLINT, CITY CHAR(15) ;EXEC SQL INCLUDE SQLCA ;GET LIST (GIVENS# ) ;EXEC SQL SELECT STATUS, CITY INTO :RANK, :CITY FROM S WHERE S# = :GIVENS# ;IF SQLCODE = 0 THEN PUT SKIP LIST (RANK, CITY); Example of Embedded SQL

  11. Example of Embedded SQL • Update: EXEC SQL UPDATE S SET STATUS = STATUS + :RAISE, WHERE CITY = ‘LONDON’ ; • Delete:EXEC SQL DELETE FROM S WHERE STATUS = NULL ; • Insert:EXEC SQL INSERT INTO S (S#, SNAM E, STATUS, CITY) VALUES(:NEWS#, :NEWSNAME, :NEWSTATUS,'LONDON’) ;

  12. Using Cursors for Sequential Access • Declare a cursor and use it as a sequential file of the query resultsEXEC SQL DECLARE X CURSOR FOR SELECT S#, SNAME, STATUS FROM S WHERE CITY = :PCITY FOR UPDATE OF STATUS ;GET LIST (PCITY, GIVENINC);EXEC SQL OPEN X ;DO WHILE ( SQLCODE = 0 )EXEC SQL FETCH X INTO :PS#, :PSNAME, :PSTATUS;PUT SKIP LIST(PSNAME, PS#, PSTATUS);EXEC SQL UPDATE S SET STATUS = STATUS + :GIVENINC WHERE CURRENT OF X END;EXEC SQL CLOSE X ;

  13. Integrity Control in SQL • Change supplier # of supplier SX from SX to SY in both tables S and SP.TRANEX: PROC OPTIONS (MAIN) : EXEC SQL WHENEVER SQLERROR GO TO UNDO;GET LIST(SX, SY); EXEC SQL UPDATE S SET S# = :SY WHERE S# = :SX; EXEC SQL UPDATE SP SET S# = :SY WHERE S# = :SX; EXEC SQL COMMIT;GO TO FINISH; UNDO: EXEC SQL ROLLBACK;FINISH: RETURN; END TRANEX;

  14. Desired Transaction Properties • Atomicity -- All operations of a transaction must be completed. If not, the transaction is aborted. • Durability -- When a transaction is completed, the database reaches a consistent state which cannot be lost. • Serializability -- The concurrent transaction are treated as though they were executed in serial order. • Isolation -- The data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.

  15. Concurrent Transactions • When transactions are interleaved, errors will occur in updating unless the DBMS has features to prevent interference between transactions CUST A BAL 100 1 2 GET CUSTA GET CUSTA A 100 A 100 BAL : BAL - 50 BAL : BAL + 25 A 50 A 125 STORE CUSTA STORE CUSTA A 50 3 4 A 125

  16. Problems with Concurrent Transactions • Lost updates - T2 is executed before T1 is committed • Uncommitted data -- T1 is rolled back after T2 accessed the uncommitted data • Inconsistent retrievals - - T1 calculates some summary data while T2 is updating the data

  17. Concurrency Control - the Scheduler • Establish the order in which the operations in concurrent transactions are executed without causing inconsistency • Locking Guarantee exclusive use of a data item to a current transaction • Timestamping Assign a global unique timestamp to each transaction. Execute operations in timestamp order.

  18. Resource Locking • Lock granularity • Database level, Table level, Page level, Row level, Field level • Lock Types • Binary locks -- locked ,Unlocked • Shared/Exclusive Locks -- Unlocked, Shared (Read) lock, Exclusive (Write) lock • Two-Phase Locking • A growing phase • A shrinking phase

  19. Deadlock • Deadly embrace • Deadlock prevention:lock all required records at beginning of transaction • Deadlock resolution:back out of one of transactions and start again User B Lock Y Wait for X User A Lock X Wait for Y X Y

  20. Optimistic vs. Pessimistic Locking • Optimistic locking • Assume the majority of database operations do not conflict. • Process each transaction in three phases: read, validation, and write. • Less restriction, may repeat process • Pessimistic locking • Assume the conflicts most likely will happen. • Lock, process, and unlock. • More restriction, no repeat process

  21. Database Recovery • Database may be damaged or lost because of some system failure. The DBMS must provide mechanisms for restoring a database quickly and accurately after loss or damage. • System failures: machine failuresdisk head crashesprogram bugsincorrect operation

  22. Database Recovery DBMS Database change log Database (current) Transaction log before image Database (backup) after image

  23. Basic Recovery Facilities • Backup facilitiesProvide periodic backup copies of the entire database • Journalizing facilitiesMaintain an audit trail of transactions and database changes • Checkpoint facilityPeriodically suspends all processing and synchronizes its files and journals • Recovery managerAllows the DBMS to restore the database to a correct condition and restart processing transactions

  24. Recovery via Restore/Rerun • Restore the latest backup copy • Reprocess the day’s transactions (up to the point of failure) against the backup copy of the database • Simple procedure • Time to reprocess transactions may be prohibitive • Sequencing of reprocessed transactions may be different from the original

  25. Recovery via Roll Back/Roll Forward Old New Roll Back (Removing Database Changes) Database without changes Database with changes Undo Before images Old (saved) Roll Forward (Repeating Database Changes) New Database with changes Database without changes Redo After images

  26. Recovery Strategies • Aborted transactions (i.e. communication interruption)Backward recovery • Incorrect dataBackward recovery or add compensation transactions • System failure (database is not damaged)Restart from the most recent checkpoint before the system failure, roll forward. • Database destruction (database damaged)Restore backup copy.Forward recovery to the check point before the loss occurred. Reprocess transactions after the check point.

  27. Security Control • Security refers to the protection of data against unauthorized disclosure, alteration, or destruction • Physical security • Password security • Authentication schemas: biometric devices, smart card

  28. Security Control • Views or subschemas • User-defined procedures • Authorization rules • Audit trails • Data encryption

  29. Authorization Rules • Grant select on Staff to User01Revoke update on Staff from User02 • Authorization rules Subject Sales department Order transactions Object Customer record Customer record Action Insert Read Constraint Credit limit <= 5000 None

  30. Database Administration • A critical success factor in managing the data resource in an organization • An indication of top management’s commitment to data resource management

  31. DA and DBA • Data Administrator (DA)Responsible for controlling the overall corporate data resource, both computerized and non-computerized.Strong managerial orientation with company-wide scope. • Database Administrator (DBA)Responsible for the control of the centralized and shared database.Tends to be more technically oriented and has a narrower, DBMS-specific scope.

  32. DBA Placement • Should not be organizationally below any group on which it imposes restrictions. • Should not be more than one level above the organizations with which it interfaces.

  33. Broad business understanding Coordination skills Analytical skills Conflict resolution skills Communication skills Negotiation skills Broad data processing background Systems Development Life Cycle Structured methodologies Database Life Cycle Database design and modeling skills Data dictionary management Desired DBA Skills Technical Managerial

  34. DBA’s Managerial Role • Support the end-user community, resolve conflicts • Enforce policies, procedures, and standards for data creation, usage, distribution, and deletion within the database. • Control data security, privacy, and integrityData are protected, reconstructable, auditable, tamperproof.Users are identifiable, authorized, monitored. • Plan, test, and Implement data backup and recoveryDatabase security officer (DSO), Disaster management • Ensure data be distributed to the right persons at the right time in the right format.

  35. DBA’s Technical Role • DBMS and utilities selection, evaluation ,and installation • Design and implementation of databases and applications • Testing and evaluation of databases and applications • Operation of the DBMS, utilities, and applicationsSystem support, performance monitoring and tuning, backup and recovery, security auditing and monitoring • Training and supporting users to use DBMS • Maintenance of the DBMS, utilities, and applicationsStorage reorganization, software upgrade, data migration and conversion

  36. Database Administration Tools • Data DictionaryStores the definition of data characteristics and relationships.May be integrated or stand-alone, active or passive.A tool for information resource management.The basis for monitoring database use and the assignment of access rights to the database users.Support data analysis-and-design activities • CASE Tools

  37. Database Administration Tools • The Data Dictionary • Different types of data dictionaries: • An integrated data dictionary is included with the DBMS, while a stand-alone data dictionary comes from a third-party vendor. • An active data dictionary is automatically updated by the DBMS, while a passive data dictionary requires a batch process to create and update the dictionary.

  38. Database Administration Tools • CASE Tools • CASE -- Computer-Aided Software Engineering • It provides an automated framework for the Systems Development Life Cycle. • Front-end CASE tools provide support for the planning, analysis, and design phases. • Back-end CASE tools provide support for the coding and implementation phases. • It is based on the use of structured methodologies and powerful graphical interfaces.

  39. Database Administration Tools • CASE Tools • Benefits of CASE tools • A reduction in development time and costs • The automation of the SDLC • Standardization of the systems development methodologies • Easier maintenance of application systems developed with CASE tools • Improve communications among the DBA, applications designers, and the end users.

  40. Database Administration Tools • CASE Tools • A CASE tool keeps track of all objects created by the systems designer in the data dictionary. • Some CASE tools provide interfaces that interact with the DBMS. • The CASE tool integrates all systems development information in a common repository.

  41. Database Administration Tools • Commercial CASE Tools • Excelerator from Intersolv, Inc. provides five components: • Graphics designed to produce structured diagrams as data flow diagrams and E-R diagrams. • Screen painters and report generators to produce the information system’s input/output formats. • An integrated repository for storing and cross-referencing the system design data. • An analysis segment to provide a fully automated check on system consistency, syntax, and completeness. • A program document generator.

  42. Database Administration Tools • Commercial CASE Tools • ERwin by LogicWorks • It produces fully documented E-R diagrams that can be displayed at different abstraction levels. • It is able to produce detailed relational designs. • Major relational DBMS vendors, such as ORACLE, provide fully integrated CASE tools for their own DBMS software as well as for RDBMSs supplied by other vendors.

  43. Future Trend • The development of distributed databases may force an organization to decentralize the data-administration function further. • The introduction of an object-oriented DBMS is very likely to add more coding in the DBA’s data modeling and design activities, thus expanding and diversifying the DBA’s job. • The rapid spread of microcomputers and local area networks tends to diffuse operational control over data, thus making centralized data administration more difficult.

More Related