1 / 15

Relational Model: Advanced Topics

Relational Model: Advanced Topics. Chapter 4. Views. An individual user’s, or an application program’s picture of the database. You can think of it as a saved query. It provides a measure of security – omitting sensitive tables or fields. Example.

toya
Download Presentation

Relational Model: Advanced Topics

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. Relational Model: Advanced Topics Chapter 4

  2. Views An individual user’s, or an application program’s picture of the database. You can think of it as a saved query. It provides a measure of security – omitting sensitive tables or fields.

  3. Example • This might be used for a salesperson’s view: CREATE VIEW Housewares AS SELECT PartNum, Description, OnHand, Price FROM Part WHERE Class = ‘HW’ ;

  4. The salesperson could write this query of the view: SELECT * FROM Housewares WHERE OnHand < 25 ; What really happens in the DBMS is: SELECT PartNum, Description, OnHand, Price FROM Part WHERE Class = ‘HW’ AND OnHand < 25 ;

  5. To the salesperson, it seems like he’s using a table named Housewares. • Any update to the Part table is immediately visible in the Housewares view (because it’s not really a table). • In Access, to make a view, create & save query. • Forms & Reports can be driven by views.

  6. CREATE VIEW Housewares (PNum, PDesc, OnHd, Price) AS SELECT PartNum, Description, OnHand, Price FROM Part WHERE Class = ‘HW’ ; This gives the salesperson a customized view, with different field names he might prefer.

  7. Views can join tables CREATE VIEW SalesCust (SNum, SLast, SFirst, CNum, CName) AS SELECT Rep.RepNum, LastName, FirstName, CustomerNum, CustomerName FROM Rep, Customer WHERE Rep.RepNum = Customer.RepNum ; A query might be written: SELECT SNum, SLast, SFirst FROM SalesCust WHERE CNum = ‘282’;

  8. An example of a query in code…

  9. Indexes • Like finding something quickly in a book. • Indexing can speed up the database performance. • Happens behind the scenes – its used by the DBMS, not the user. • Most helpful where tables have thousands, tens of thousands, or hundreds of thousands of records..

  10. CreditLimit Index

  11. Security • Assigning privileges to different users GRANT SELECT on Customer TO Jones GRANT INSERT ON Part TO Smith, Park

  12. Integrity Rules • Entity Integrity • No field that is part of a primary key can be NULL. • NULLs are when a value is missing, unknown or inapplicable

  13. Referential Integrity • Relationships are not very obvious. • Picture a DB with 20 tables, where tables have 30 fields. • Foreign Key FOREIGN KEY (RepNum) REFERENCES Rep In the customer table If they don’t match (perhaps a rep has been deleted)- then referential integrity is compromised.

  14. Structure Changes ALTER TABLE Customer ADD CustType CHAR(1) ALTER TABLE Customer CHANGE COLUMN CustomerName TO CHAR(40) ALTER TABLE Part DELETE Warehouse DROP TABLE SmallCust

  15. Many to Many Relationships • Students to Teachers • Products to Categories • Musicians to Bands Need 3 Tables Musicians (musicianID, name, …) Bands (bandID, name, label…) Musicians_bands (musicianID, bandID)

More Related