Database Database


Download 466 b.
bet19/19
Sana14.08.2018
Hajmi466 b.
1   ...   11   12   13   14   15   16   17   18   19

Some relationships are obvious.

  • Burger and fries.
  • Some relationships are meaningless.

    • Hardware store found that toilet rings sell well only when a new store first opens. But what does it mean?


    Examples

    • Examples

      • Are there groups of customers? (If so, we can cross-sell.)
      • Do the locations for our stores have elements in common? (So we can search for similar clusters for new locations.)
      • Do our employees (by department?) have common characteristics? (So we can hire similar, or dissimilar, people.)
    • Problem: Many dimensions and large datasets



    Examples

    • Examples

      • Customer location and sales comparisons
      • Factory sites and cost
      • Environmental effects
    • Challenge: Map data, multiple overlays





    Data and information are valuable assets.

    • Data and information are valuable assets.

    • There are many databases and applications in an organization.

    • Someone has to be responsible for organizing, controlling, and sharing data.

    • Data Administrator (DA)



    Provide centralized control over the data.

    • Provide centralized control over the data.

      • Data definition.
        • Format
        • Naming convention
      • Data integration.
      • Selection of DBMS.
    • Act as data and database advocate.

      • Application ideas.
      • Decision support.
      • Strategic uses.
    • Coordinate data integrity, security, privacy, and control.



    Install and upgrade DBMS.

    • Install and upgrade DBMS.

    • Create user accounts and monitor security.

    • In charge of backup and recovery of the database.

    • Monitor and tune the database performance.

    • Coordinate with DBMS vendor and plan for changes.

    • Maintain DBMS-specific information for developers.



    The schema is a namespace often assigned to users so that table names do not have to be unique across the entire database.

    • The schema is a namespace often assigned to users so that table names do not have to be unique across the entire database.

    • The catalog is a container with the goal of making it easier to find schema, but is probably not supported by any DBMS yet.



    Data about data

    • Data about data

      • Example: a system table that contains a list of user tables.
    • SQL standard uses the information_schema views that retrieve data from the definition_schema



    Planning

    • Planning

      • Determine hardware and software needs.
    • Design

      • Estimate space requirements, estimate performance.
    • Implementation

      • Install software, create databases, transfer data.
    • Operation

    • Growth and Change

      • Monitor and forecast storage needs.
    • Security

      • Create user accounts, monitor changes.


    Estimation

    • Estimation

      • Data storage requirements
      • Time to develop
      • Cost to develop
      • Operations costs


    Teamwork

    • Teamwork

      • Data standards
      • Data repository
      • Reusable objects
      • CASE tools
      • Networks / communication
    • Subdividing projects

      • Delivering in stages
        • User needs / priorities
        • Version upgrades
      • Normalization by user views
        • Distribute individual sections
        • Combine sections
      • Assign forms and reports


    Standards for application programming.

    • Standards for application programming.

      • User interface.
      • Programming standards.
        • Layout and techniques.
        • Variable & object definition.
      • Test procedures.
    • Data access and ownership.

    • Loading databases.

    • Backup and recovery plans.

    • User and operator training.



    Monitoring usage

    • Monitoring usage

      • Size and growth
      • Performance / delays
      • Security logs
      • User problems
    • Backup and recovery

    • User support

      • Help desk
      • Training classes


    Detect need for change

    • Detect need for change

      • Size and speed
      • Structures / design
        • Requests for additional data.
        • Difficulties with queries.
      • Usage patterns
      • Forecasts
    • Delays in implementing changes

      • Time to recognize needs.
      • Time to get agreement and approval.
      • Time to install new hardware.
      • Time to create / modify software.


    Backups are crucial!

    • Backups are crucial!

    • Offsite storage!

    • Scheduled backup.

      • Regular intervals.
      • Record time.
      • Track backups.
    • Journals / logs

    • Checkpoint

    • Rollback / Roll forward



    Physical security

    • Physical security

      • Protecting hardware
      • Protecting software and data.
    • Logical security

      • Unauthorized disclosure
      • Unauthorized modification
      • Unauthorized withholding




    Hardware

    • Hardware

      • Preventing problems
        • Fire prevention
        • Site considerations
        • Building design
      • Hardware backup facilities
        • Continuous backup (mirror sites)
        • Hot sites
        • Shell sites
        • “Sister” agreements
      • Telecommunication systems
      • Personal computers


    Backup data.

    • Backup data.

    • Backup hardware.

    • Disaster planning and testing.

    • Prevention.

      • Location.
      • Fire monitoring and control.
      • Control physical access.


    “Insiders”

    • “Insiders”

      • Hiring
      • Termination
      • Monitoring
      • Job segmentation
      • Physical access limitations
    • Consultants and Business alliances

      • Limited data access
      • Limited physical access
      • Paired with employees


    Unauthorized disclosure.

    • Unauthorized disclosure.

    • Unauthorized modification.

    • Unauthorized withholding.



    User identification

    • User identification

    • Accounts

      • Individual
      • Groups
    • Passwords

      • Do not use “real” words.
      • Do not use personal (or pet) names.
      • Include non-alphabetic characters.
      • Use at least 6 (8) characters.
      • Change it often.
      • Too many passwords!


    Limit access to hardware

    • Limit access to hardware

      • Physical locks.
      • Video monitoring.
      • Fire and environment monitors.
      • Employee logs / cards.
      • Dial-back modems
    • Monitor usage

      • Hardware logs.
      • Access from network nodes.
      • Software and data usage.
    • Background checks

      • Employees
      • Consultants


    Operating system

    • Operating system

      • Access to directories
        • Read
        • View / File scan
        • Write
        • Create
        • Delete
      • Access to files
        • Read
        • Write
        • Edit
        • Delete
      • DBMS usually needs most of these
      • Assign by user or group.


    GRANT privileges

    • GRANT privileges

    • REVOKE privileges

    • Privileges include

      • SELECT
      • DELETE
      • INSERT
      • UPDATE
    • Objects include

      • Table
      • Table columns (SQL 92+)
      • Query
    • Users include

      • Name/Group
      • PUBLIC






    Permissions apply to entire table or query.

    • Permissions apply to entire table or query.

    • Use query to grant access to part of a table.

    • Example

      • Employee table
      • Give all employees read access to name and phone (phonebook).
      • Give managers read access to salary.
    • SQL

      • Grant
      • Revoke




    Set up a secure workgroup

        • Set up a secure workgroup
          • Create a new Admin user.
          • Enable security by setting a password
          • Remove the original Admin user.
    • Run the Security Wizard in the database to be secured.

    • Assign user and group access privileges in the new database.

        • Encrypt the new database.
    • Save it as an MDE file.



    Protection for open transmissions

    • Protection for open transmissions

    • Single key (AES)

    • Dual key

      • Protection
      • Authentication
    • Trap doors / escrow keys

    • U.S. export limits

      • 64 bit key limit
      • Breakable by brute force
        • Typical hardware:2 weeks
        • Special hardware: minutes


    Using Bob’s private key ensures it came from him.

    • Using Bob’s private key ensures it came from him.

    • Using Alice’s public key means only she can read it.









    Definition

    • Definition

    • Advantages / Uses

    • Problems / Complications

    • Client-Server / SQL Server

    • Microsoft Access



    Multiple independent databases

    • Multiple independent databases

      • Each DBMS is a complete DBMS (engine, queries, locking, transactions, etc.)
      • Usually on different machines.
      • Usually in different locations.
    • Connected by a network.

    • Might be different environments

      • Hardware
      • Operating System
      • DBMS Software


    C.J. Date

    • C.J. Date

    • Rule 0: Transparency: the user should not know or care that the database is distributed.

      • Local autonomy.
      • No reliance on a central site.
      • Continuous operation.
      • Location independence.
      • Fragmentation independence (physical storage).
      • Replication independence.


    Each database can continue to run even if portion fails.

    • Each database can continue to run even if portion fails.

    • Data and hardware can be moved without affecting operations or users.

      • Expanding operations.
      • Performance issues.
    • System expansion and upgrades.

      • Add new section without affecting others.
      • Upgrade hardware, network and DBMS.


    Business operations are often distributed

    • Business operations are often distributed

      • Work and data are segmented by department.
      • Work and data are segmented by geographical location.
    • Improved performance

      • Most updates and queries are performed locally.
      • Maintain local control and responsibility over data.
    • Can still combine data across the system.

    • Scalability and expansion

      • Add on, not replacement.


    Design administration plan.

    • Design administration plan.

    • Choose hardware and DBMS vendor, and network.

    • Set up network and DBMS connections.

    • Choose locations for data.

    • Choose replication strategy.

    • Create backup plan and strategy.

    • Create local views and synonyms.

    • Perform stress test: loads and failures.



    Networks are slow

    • Networks are slow

      • Drives: 20 - 60 MB per sec.
      • LANs: 1-10 MB per sec (10-100 mbps).
      • WANs: 0.01 - 5 MB per sec.
      • Faster is possible but expensive!
      • SANs: 10-100 MB per sec.
    • Goal is to minimize transmissions.

      • Each system must be capable of evaluating queries--preferably SQL.
      • Results depend heavily on how the system joins tables.


    Example

    • Example

      • NY: Customers: 1 M rows
      • LA: Production: 10 M rows
      • Chicago: Sales: 20 M rows
      • Query: List customers who bought blue products on March 1
      • Bad idea #1
        • Transfer all rows to Chicago
        • Then JOIN and select.
      • Better idea #2 (probably)
        • Transfer blue products from LA to Chicago
      • Better idea #3
        • Get sale items on March 1
        • Get blue products from LA
        • Send C# to NY


    Goals

    • Goals

      • Minimize transmissions
      • Improve performance
      • Support heavy multiuser access.
    • Problems

      • Updating copies
        • Bulk transmissions
        • Site unavailable
      • Concurrency
        • Easier for two people to change the same data at the same time.
    • Decision support systems.

    • Data warehouse.



    Each DBMS must maintain lock facility.

    • Each DBMS must maintain lock facility.

    • To update, each DBMS must utilize and recognize other lock mechanisms and return codes.

    • Each DBMS must have a deadlock resolution protocol that recognizes the distributed databases.

      • Random wait.
      • Optimistic updates.
      • Two-phase commit.


    Two (or more) separate lock managers.

    • Two (or more) separate lock managers.

    • DBMS initiating update serves as the coordinator.

    • Two phases

      • Coordinator sends message and data to all machines to “get ready.”
      • Local machines save data in logs, verify update status and return message.
      • If all locals report OK, then coordinator writes log and instructs others to proceed. If any fail, it sends Rollback message.






    Database Links

    • Database Links

      • Full database names.
      • CONNECT command.
    • Linking through synonyms.

      • CREATE SYNONYM …
      • Central control over permissions.
    • Linking through Views/queries.

      • CREATE VIEW AS …
      • Can assign local permissions.
    • Linking through stored procedures.

      • DELETE …
      • Strong control over actions.




    Not a distributed database.

    • Not a distributed database.

      • Data file stored on server.
      • Server is passive, appears as giant disk drive to PC.
      • PC processes all data.
      • Retrieves all needed data across the network.
    • Performance improvements.

      • Indexes are crucial.
      • Store some data on each PC (replication).
      • Store applications on PC (graphics & forms).
      • Convert to SQL-Server




    One machine machine is dominant (server) and handles data for many clients.

    • One machine machine is dominant (server) and handles data for many clients.

    • Client machines handle front-end tasks and small data tables that are not shared.





    Server Databases

    • Server Databases

    • Client front-end

    • Middle

      • Locate databases
      • Business rules
      • Program code































    Do'stlaringiz bilan baham:
    1   ...   11   12   13   14   15   16   17   18   19


    Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2017
    ma'muriyatiga murojaat qiling