1 / 14

Databases and Data Representation

Databases and Data Representation. Relationships. Relation: one-to-many. LectID. Name. Course. Students. LectID. Building. Room. OfficeID. Primary key. Foreign key. Database Views. CREATE VIEW StrainOffices AS SELECT OfficeID, LectID, Room FROM Offices WHERE Building = ‘Strain’.

alexandra-mcfadden
Download Presentation

Databases and Data Representation

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. Databases and Data Representation CS360 Windows Programming

  2. Relationships • Relation: one-to-many LectID Name Course Students LectID Building Room OfficeID Primary key Foreign key CS360 Windows Programming

  3. Database Views • CREATE VIEW StrainOffices AS SELECT OfficeID, LectID, Room FROM Offices WHERE Building = ‘Strain’ LectID Building Room OfficeID LectID Room OfficeID CS360 Windows Programming

  4. Database Views • Views do not store data – they are “virtual” tables • If we query a view, tuples are obtained from the base table so that the query can be answered • SELECT OfficeID, RoomFROM StrainOfficesWHERE LectID = 1 LectID Room OfficeID Room OfficeID CS360 Windows Programming

  5. Database Views • We can rename the columns in the view if we want • CREATE VIEW StrainOffices(OId, Lid, RoomNum) AS SELECT OfficeID, LectID, Room FROM Offices WHERE Building = ‘Strain’ LId RoomNum OId CS360 Windows Programming

  6. Database Joins LectID Name Course Students LectID Building Room OfficeID Primary key Foreign key CS360 Windows Programming

  7. Joins • SELECT * FROM Lectures INNER JOIN Offices ON Lecturers.LectID = Offices.LectID ORDER BY Offices.LectID CS360 Windows Programming

  8. Your Turn • SELECT Name FROM Lecturers INNER JOIN Offices ON Lecturers.LectID = Office.LectIDINNER JOIN Advisees ON Lecturers.LectID = Advisees.AdvIDWHERE Building = ‘Strain’ AND Name = ‘Harry’ AdvID LectID Name LectID Name Course Students OfficeID LectID Building Room CS360 Windows Programming

  9. Connecting to MySQL • C# can connect to MySQL • Need to download a .NET connector • http://dev.mysql.com/downloads/connector/net/1.0.html • Need the MySql.Data.dll • I’ve placed it in CS360 Pub under MySQL Connector\bin\.NET 1.1 CS360 Windows Programming

  10. Connecting to MySQL CS360 Windows Programming

  11. Connecting to MySQL using MySql.Data.MySqlClient; string connStr = "server=cs445.cs.pacificu.edu; user id=shereen; password=abc123; database=shereen;"; MySqlConnection conn = null; MySqlDataAdapter da = null; MySqlDataReader reader = null; MySqlCommand cmd = null; CS360 Windows Programming

  12. Connecting to MySQL try { conn = new MySqlConnection(connStr); conn.Open(); cmd = new MySqlCommand("SELECT * FROM pet", conn); reader = cmd.ExecuteReader(); lb2.Text = ""; while (reader.Read()) { lb2.Text = lb2.Text + reader.GetString(0) + "\n"; } } CS360 Windows Programming

  13. Connecting to MySQL catch (MySqlException ex) { lb2.Text = "Exception accessing MySQL server: " + ex.Message; } catch (Exception ex) { lb2.Text = "Exception accessing database list: " + ex.Message; } finally { if (reader != null) reader.Close(); if (conn != null) conn.Close(); } CS360 Windows Programming

  14. CS360 Windows Programming

More Related